1

I need to delete about 900m rows from a table that has 6b rows. Since big deletes cause big problems for the transaction log, I'm deleting the data in small batches. What puzzles me is SQL Server does not like to commit each chunked delete.

Let's say I delete it in batches of 1m rows:

DECLARE @N int = 1000000

DELETE TOP (@N)
    FROM    my_table
    WHERE   ...

In a separate query, I monitor the status of the delete:

-- Before executing the first query
sp_spaceused 'my_table' -- row_count = 6b

-- During execution
sp_spaceused 'my_table' -- row_count = decreasing from 6b -> 5.999b

And then it just hung! I can tell that 1m rows have been removed from my_table by peeking at the row count, but the first query just keeps going on and on.

What could cause this behavior? Another big table has a Foreign Key relationship referencing my_table.ID. Could FK validation be the culprit here?

Database is SQL Server 2012.

  • what does the execution plan look like? Does it have operators to delete rows from other indexes on the table? – Martin Smith Nov 17 '19 at 0:10
  • Is this a heap or table with clustered index? – Learning_DBAdmin Nov 17 '19 at 6:08
  • Take Care When Scripting Batches, and it seems to be lock escalation. – Denis Rubashkin Nov 17 '19 at 7:41
  • You can make batch size little smaller. how much percent of data is to be kept ?wht is requirement like ? – KumarHarsh Nov 18 '19 at 11:44
1

Yes, it will definitely add overhead if you have foreign keys pointing to the table that you are trying to clear records from. This is because it needs to ensure that the constraint is not violated once the data has been removed. I've personally seen deletes take days with foreign key checks, but hours if you remove the foreign keys before performing the DELETE.

Disabling the foreign keys will not help in your scenario as you can only disable a foreign key check for INSERT and UPDATE statements. I recommend that you remove any dependent data from the related tables, then remove the foreign key (if possible), and then remove the data from the base table. Once the data has been completely removed you can re-add the foreign key relationship.

  • 1
    thanks. The DBAs just informed me that there was a maintenance at the same time so lock escalation is likely the culprit here. – Mike Henderson Nov 17 '19 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.