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?