Sql-server – sql bulk delete is much slower

sql server

I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete

  • DELETE TOP (n) syntax, then loop until now more needs to be deleted

it seems we controlled the log size problem. however, the performance downgraded a lot. for example, if I delete the data from the first table A in one batch (delete from …), it takes about 9 minutes. Now, if I delete in a batch of 2000, after the whole night, it does not finish yet. If I get the # of rows that will be deleted, then batch delete 10% of it in each loop (so, about 200,000 for each batch), it takes about 40 minutes.

Is this normal? one thing I want to point out is: there is no primary key in these 2 tables, although they have non-cluster index. it was designed by previous developer who never thought we would have so much data to process. Now, we have to live with this, change database structure is not an option. Could it because of this that makes the delete slow?

Any suggest of what can cause the slow?

Best Answer

Considering the symptomp of gradual degradation after each batch I would guess is caused by DELETE leaving behind ghosted records, see SQL Server “empty table” is slow after deleting all (12 million) records? Subsequent deletes have to skip over all those ghosts, until they are reclaimed. For a one time operation it would make more sense to copy the data to be kept into a new table, then use partition switch tricks to switch it in instead of the old data, then truncate the old data. If you can't do ALTER TABLE .. SWITCH you can use the old sp_rename trick, but bear in mind that sp_rename does not preserve security permissions etc on the table (it is a different table with the same name).

If this deletion is to be done regularly, then you may look into a sliding window scenario.

Related Question