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?
thanks
Best Answer
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.