Is it possible to pause an SQL query?

Question

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.

Answer 1

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

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

Answer 2

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.