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
COMMITworks 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/enabling in transaction makes no difference.
- Dropping and re-creating FK and using
DELETEworks fine - at least until I need
GHOST CLEANUPthread (
DBCC TRACEOFF(661, -1)) makes original batch complete at full speed, but it's not the real solution for production environment.
Why does disabling FK and deleting table's content trigger
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?
There are no triggers in DB. All FKs have empty
ON UPDATE/DELETE action.