Erland Sommarskog (7/25/2013)
So the problem is exactly the foreign keys. CASCADE or NO ACTION, if you delete a row from a table, and there is an FK constraint from a big table and the FK column is unindexed, the delete will take long time, because SQL Server will have to scan that big table.
Yeah, that was it.
I'm going to add indexes to the FKs on those related tables and see if that sorts it properly.