CODEWITHSUNDEEP
t-sqlsql-server-2008
Pure.Krome
Pure.Krome

Reputation: 86957

Is it possible to pause an SQL query?

I've got a really long running SQL query (data import, etc). It's crap - it uses cursors and it running slowly. It's doing it, so I'm not too worried about performance.

Anyways, can I pause it for a while (instead of canceling the query)?

It chews up a a bit of CPU so i was hoping to pause it, do some other stuff ... then resume it.

I'm assuming the answer is 'NO' because of how rows and data gets locked, etc.

I'm using Sql Server 2008, btw.

Upvotes: 2

Views: 23605

Answers (5)

DavidStein
DavidStein

Reputation: 3179

Instead of pausing the script, perhaps you could use resource governor. That way you could allow the script to run in the background without severely impacting performance of other tasks.

MSDN-Resource Governor

Upvotes: 0

Sean Reilly
Sean Reilly

Reputation: 21836

Not only can you not pause it, doing so would be bad. SQL queries hold locks (for transactional integrity), and if you paused the query, it would have to hold any locks while it was paused. This could really slow down other queries running on the server.

Rather than pause it, I would write the query so that it can be terminated, and pick up from where it left off when it is restarted. This requires work on your part as a query author, but it's the only feasible approach if you want to interrupt and resume the query. It's a good idea for other reasons as well: long running queries are often interrupted anyway.

Upvotes: 4

ChrisLively
ChrisLively

Reputation: 88064

Click the debug button instead of execute. SQL 2008 introduced the ability to debug queries on the fly. Put a breakpoint at convenient locations

Upvotes: 3

TheJacobTaylor
TheJacobTaylor

Reputation: 4143

When working on similar situations, where I was trying to go through an entire list of data, which could be huge, and could tell which ones I have visited already, I would run the processing in chunks.

update or whatever
where (still not done)
limit 1000

And then I would just keep running the query until there are no rows being modified. This breaks the locks up into reasonable time chunks and can allow you to do thinks like move tens of millions of rows between tables while a system is in production.

Jacob

Upvotes: 0

Sheldon
Sheldon

Reputation: 2628

The best approximation I know for what you're looking for is

BEGIN
    WAITFOR DELAY 'TIME';
    EXECUTE XXXX;
END;
GO

Upvotes: 9

Related Questions