Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to replace data in table referenced by FKs in other tables on SQL Server. There are more than 1M rows in both referenced and referencing table and I want to make the replacement atomic. Obvious solution would be:

BEGIN TRAN
ALTER TABLE TabReferencing NOCHECK CONSTRAINT MyForeignKey
DELETE FROM TabReferenced
INSERT INTO TabReferenced (...)
ALTER TABLE TabReferencing CHECK CONSTRAINT MyForeignKey
COMMIT

If those statements (executed one-by-one or in batch, over network on on local SQL Server) take longer than a few seconds to complete, things go nasty. GHOST CLEANUP background thread on server starts up few moments after DELETE and starts using 100% CPU and quite a lot of IO, degrading overall performance to unacceptable level (500 INSERTs/min compared to 50000 INSERTs/min without cleanup).

So far I have tried following workarounds:

  • Disabling FK before beginning transaction and re-enabling it after COMMIT works fine (quick enough), but if process (or network) fails during INSERTs, I end up with table with no FKs.
  • Deleting data from both referencing and referenced table and NOT touching FK works fine, but I don't want to repopulate referencing table due to it's size.
  • Dropping and re-creating FK instead of dis/en-abling in transaction makes no difference.
  • Dropping and re-creating FK and using TRUNCATE instead of DELETE works fine - at least until I need DELETE with WHERE clause.
  • Disabling GHOST CLEANUP thread (DBCC TRACEOFF(661, -1)) makes original batch complete at full speed, but it's not the real solution for production environment.

The proper question is: Why does disabling FK and deleting table's content triggers GHOST CLEANUP and just deleting table or truncating table does not? Why does cleanup with disabled/dropped FK takes way longer than same cleanup with FK enabled?

share|improve this question

migrated from stackoverflow.com Nov 20 '11 at 23:24

This question came from our site for professional and enthusiast programmers.

    
Could it be triggers with cascading delete? –  user4602 Nov 21 '11 at 7:03
    
There are no triggers in DB. All FKs have empty ON UPDATE/DELETE action. –  MagnatLU Nov 21 '11 at 11:12
add comment

2 Answers 2

Why does disabling FK and deleting table's content triggers GHOST CLEANUP and just deleting table or truncating table does not?

From your question it looks like you understand ghost records and the ghost cleanup process but I'll summarise for future readers.

When a row is deleted, it isn't physically removed from the page. Instead, it is marked as ghosted and a background task (ghost cleanup) comes along later and removes the records entry from the page slot array (note the data isn't overwritten or zero'd out).

When you TRUNCATE or DROP the table records aren't deleted/ghosted individually, entire pages are de-allocated. See the two excellent answers from @NickChammas and @MartinSmith describing the mechanics of drop and truncate in Why use both TRUNCATE and DROP?

That's why you aren't seeing the ghost cleanup task working over time following a TRUNCATE or DROP.

Why does cleanup with disabled/dropped FK takes way longer than same cleanup with FK enabled?

If you've stated the sequence of events accurately, this process is not what is triggering the ghost cleanup process. The ghost cleanup task doesn't start removing deleted records until the transaction that deleted them commits (it can't as they're locked).

So, either something else is at work or you've encountered a bug. KB978430 could apply, if that doesn't work I'd be inclined to log a support call.

share|improve this answer
add comment

Since you are re-enabling the foreign key constraint, you must be inserting at least a subset of the rows that were already in the table. Consider rewriting your query from a blanket delete. UPDATE rows that should remain, and INSERT new rows, and DELETE only rows that should be deleted.

If you're on SQL Server 2008, the merge statement is a good way to do that.

share|improve this answer
    
Good point with MERGE statement, but it's not feasible in this case. Those statements are generated by ORM as part of much larger and more complex business process. I've cut irrelevant parts out for clarity to narrow down problem. Besides, even withous INSERTs CPU usage skyrockets a few seconds after issuing DELETE. –  MagnatLU Nov 20 '11 at 6:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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