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?