I had the same problem last week, and tried various techniques as described above. I was given a piece very useful advice by our DBA....
...if you can, remove the clustered index on any table records are being deleted from if you can, and put it back when you''re finished.
When the clustered index is there, each individual delete causes the physical records to be shuffled up on disc to ensure they are all physically contiguous,
which is a huge performance overhead on mass deletes.
I disagree here.
The reality of delets do not cause the pages to adjust in any way.
In fact the gaps are just left which means you have available space left in all affected pages and actually the data is still there if you look at with special commands the slot is marked free, unless a page is completely deallocated.
For example if this were the layout
And I issued a DELETE where Alpha in (b, c, g, i, n, o, z) And Alpha Between p AND t Then this is what is happens to the page
-- b is marked deleted and recoverable
-- c is marked deleted and recoverable
-- g is marked deleted and recoverable
-- i is marked deleted and recoverable
-- n is marked deleted and recoverable
-- o is marked deleted and recoverable
-- Page 4 is marked recoverable
-- p is marked deleted and recoverable
-- q is marked deleted and recoverable
-- r is marked deleted and recoverable
-- s is marked deleted and recoverable
-- t is marked deleted and recoverable
Page 5 -- (Technically becomes Page 4 but it is poitner based so the names really mean nothing)
-- Page 6 is marked recoverable
-- z is marked deleted and recoverable
In the case of indexes with deletes the indexes root and leaf pages may have slots and pages marked freed but the do not move data at all.
Now afterwarss issue a DBCC SHOWCONTIG and you will see fragmentation on the indexes and data pages.
By issuing a DBCC DBREINDEX or DBCC INDEXDEFRAG you can reclaim this lost space.
What the issue is is the logging of the transactions and the log file needing to grow periodically on large amount of deletes.
That is what the issue is here.
However that said if auto update statistics is on, then at the end of the delete cycle(s) you may experience a slow down if the number of deletes causes this to occurr. You may want to set off during the while handled deletes for an improvement and do them yourself when doen with sp_updatestats then turn back on. Also after a large number of deletes running DBCC UPDATEUSAGE can be helpfull to performance.