I have a large database, about 30GB. In order to delete some data from a table (most data is in this table, simply because the # of rows), I have to do delete in batch. However, if I delete in too many batches, the system becomes very slow. So, what we do is: query the # of data that will be deleted, then use 1/10 of the # as batch size.

When I test this, I found a weird thing.

since it has lots of data to be deleted, the delete batch loop takes a long time to finish. during the delete, when I use sp_who2 to check, I can see the CPU time and DiskIO of 2 SPID keep changing. One is for DELETE and one is for SELECT. the delete is for bacth delete. but the SELECT is for getting the # of data that will be deleted.

I'm using the sqlcommand in c# to do this. The SELECT was done first, once get the #, then we do DELETE. The log actually shows the result of SELECT. My question is: why sp_who2 still shows the CPU time and DiskIO changing? Should it be done already? what can cause this?

and at the same time, the CPU Time and DiskIO for delete is changing too.

anyone has an explaination of this?


  • 2
    select still has to fetch the actual data from disk which you've selected, even long after the actual query execution's completed. – Marc B Jul 13 '12 at 16:57

If there's a where clause in the DELETE statement, then the server still needs to perform a SELECT internally to the DELETE statement in order to delete only the specified records.

You may be only issuing one DELETE statement, but that's a convenience of SQL. The Database software has to process that statement and perform the necessary logic to do the job under the hood. This is one of those things it does that you don't normally think about until you happen to notice it, as you did now.

| improve this answer | |
  • no, that's not it. the code is like: int count = (get the # of data that will be deleted); log.output count; // so we know the # .... // some other processing loop { .... batch delete } the select is the one in (get the # of data that will be deleted). since it output count in log, I would think the query finished already. am I right? thanks – urlreader Jul 13 '12 at 17:20

I would suggest that instead of deleting the rows rather create a new table which will have the data you would like to keep in the table. Create appropriate constraints and index.

Then drop the original table and then rename the new table. This would be best if you are deleting lots of data.

Deleting batches is good when you delete only very few data as compared to the total data.

Also,why do you need a select?Why dont you use a delete top (5000) from table where ?

Also, I am sure it is more or less a maintiannce kind of taks why dont create a schedueled job written in TSQL rather than using the C# code.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy