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?

  • 3
    Need more detail, for example what does your DELETE look like, how you define TOP (e.g. is there an ORDER BY or do you like random), why is there no primary key or clustered index, what recovery model, what else are you doing in the loop (e.g. backing up the log, checkpoint), ...? Jul 10, 2012 at 15:43
  • Aaron's comment is exactly right - need more detail. I'd like to add that it would help if we knew the high level structure of the table. Does it have any kind of Date field you could index on and then delete using the date? If not, is there any field that might be used to index / order this data? Also, have you looked at the explain plans to see what the difference is?
    – Jim
    Jul 10, 2012 at 15:54
  • 2
    Are there any delete related triggers on the table?
    – Bridge
    Jul 10, 2012 at 16:14

2 Answers 2


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.

  • can you kindly explain how can i start the Ghost clean Up Task ? My second query is - ho can i check that the database is marked as having some ghost records ?
    – Pankaj
    Jul 11, 2012 at 15:54
  • The Ghost Cleanup Tasks runs on its own. To see the number of deleted records, check ghost_record_count in [sys.dm_db_index_physical_stats](seehttp://msdn.microsoft.com/en-us/library/ms188917.aspx) Jul 11, 2012 at 17:21

each table has about 2.8M rows. now I need to delete about 1.9M rows from each table

  1. Create a Stored Proc.
  2. Insert .9M rows in a Local Temporary Table.
  3. Truncate User Defined Table.
  4. Insert all Local Temporary Table records in the User Defined Table.

Note - In case you have any Delete Trigger, Please attach the SQL Profiler for any Performance hinderance in you query.

Do the same process for another table. Hope this will help you.

Sample Stored Proc

Begin  Try
    Begin Tran
        --Your queries
    Commit Tran
End Try

Begin Catch
    Rollback Tran
End Catch
  • Why would you use a temp table for this?
    – JNK
    Jul 10, 2012 at 16:15
  • @JNK Sir, I want to keep the required records somewhere. So either it can be placed in UDT that can have a Unique GUID like name and can be deleted finally in the above process or I can use Local Temporary table. The Table usage will be just to keep the records temporarily.
    – RGI
    Jul 10, 2012 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.