I have a relatively small database, maybe a few hundred thousand rows, and we have a stored procedure that is meant to clear out a 'Dealer' (the application aggregate root) and all of it's related data. The database lives on a v12 Azure SQL instance, and when it hits one particular table, it consumes 100% of the instance's DTU/CPU and takes a really long time to finish.
I believe the issue is that the table has a ton of foreign keys, probably about 30. Looking at the execution plan you can see that it's doing a ton Nested Loop
with index scans to find dependent rows. All of these rows were already delete prior to the attempt to delete the rows from this particular table, so these loops are actually pointless.
My question is, would it be more efficient to disable the foreign key constraints on these dependent tables, do the delete, and then re-enable to constraints? Would there be some negative effect to doing that, aside from the fact the it's technically possible that some bad data gets inserted while the constraints are disabled. Or is there an even better way to accomplish what I want to accomplish?
UPDATES This is a v12 Azure SQL database, it is not full blown SQL Server 2012 in a VM. I am not batching any deletes, and the offending statement is part of a larger SPROC. I identified this particular table delete by tearing the sproc apart and running bits and pieces of it separately. When it got to this table, it took literally 15 minutes to complete. It will affect on the order of hundreds of rows, varying depending on the state of the particular 'Dealer' we're clearing out.
We cannot do soft deletes in this case because usually we're clearing this dealer out so it can be replaced (soft deletes would cause duplication and would require a lot of code to work around). I have investigated other types of blocking and wait states and found nothing else that seemed to be negatively affecting this. As I mentioned this database is relatively small, and it's mostly used for very simple CRUD operations.
Here's is the execution plan, it's huge.
The XML version of the execution plan can be found here: https://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2