1

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. DELETE FROM Chemical Execution plan

The XML version of the execution plan can be found here: https://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2

12
  • 3
    How did you narrow it down to this specific table? Can you share the execution plan that you captured? How many rows in this table are affected? Did you investigate for blocking or wait types while this query was running? Do any of the tables have cascade settings or triggers? Have you considered a "soft delete" approach where you mark them as hidden for now and then delete them for real during a maintenance window where you could temporarily disable constraints with a lot less risk? Apr 26, 2018 at 19:20
  • Yes, is this Azure SQL Database, or a SQL Server 2012 instance in an Azure VM. Apr 26, 2018 at 19:29
  • Are you batching your deletes?
    – SQLDevDBA
    Apr 26, 2018 at 19:29
  • @AaronBertrand the "soft" delete is an awesome approach and has worked for me in the past with great success. It's also allowed us to "recover" data without having to restore. :)
    – SQLDevDBA
    Apr 26, 2018 at 19:40
  • 1
    @AaronBertrand See updates Apr 26, 2018 at 19:46

2 Answers 2

2

As you note the plan shows many index scans[1], a couple of the indexes multiple times such as Facilities.IX_CustomerID. That will be why is it spinning the CPU resource: those indexes will be in the buffer pool so little IO is needed and the CPUs are being made to chew them, probably in their entirety, repeatedly.

If this is from a simple DELETE statement and all the other activity is the engine enforcing foreign key constraints then I would check for foreign keys without supporting indexes. For instance one of the scans of [Facilities]" Index="[IX_CustomerID] has the search predicate [DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID], which suggests an index covering [Facilities].[SurfaceCleanerID] is not present or for some reason can not be used. When you define a foreign key an index is not automatically created to go with it which can surprise people and that would explain a scan of the table/cluster rather than multiple seeks on an index. In any cases where those indexes are the supporting indexes for the foreign keys, make sure that they are correctly defined and don't have badly out-of-date statistics.

If the delete operation isn't that simple then make sure any joins and sub-queries used in deciding which rows will be deleted are supported by appropriate indexes and that the joining and filtering predicates are arranged such that they are sargable where ever possible.

Failing the above (i.e. all the FKs and any other filtering & joining activity all look fine with regard to having indexes they could potentially seek on), the next straw I'd clutch for is: if you are deleting many rows at once (DELETE WHERE IN (<a result that could be a long list>)) then I've seen that make SQL Server scan the index instead of using multiple seeks. This feels a bit nasty[2] but: check this by trying to confirm the behaviour with just one row and if that changes at least some of the scans to seeks (and improves performance as a result) consider cursoring through the rows that need to be removed and doing them one at a time.

[1] Which as they are clustered index scans are effectively table scans which for large tables means a lot of data is being touched.
[2] Because it is nasty. But if it is nasty and works, it still works!

5
  • FWIW, yes this is a simple DELETE FROM Chemical WHERE DealerID = @DealerID. I will certainly investigate places where I can add indexes on those foreign keys and see what impact that has. Apr 27, 2018 at 12:56
  • 1
    Note that this supporting Foreign Keys with indexes whenever the PK table will have deletes is really not optional. In addition to the performance of deletes, the massive amounts of locking required by deleting a row here will cause blocking and deadlocks in your application. Apr 27, 2018 at 19:24
  • @DavidBrowne-Microsoft Thanks for the advice, it's actually really rare to delete a row from this particular table. This is a special "delete everything from this user so he can start over" kind of situation. That being said, I did add the indexes as suggested and the query plan looks much better, I hope to get some testing on it soon to verify that it reduces the CPU/DTU load. Apr 27, 2018 at 21:17
  • @CodingGorilla: Unless space is at a premium or your tables see a lot of write activity, having the indexes in place is usually worth it even if operations like this where they are used are rare. Though I suspect that you'll find that you have other queries that will be able to make good use of them too, making them even more worthwhile. Apr 27, 2018 at 23:45
  • These indexes made a MASSIVE impact, a recent delete took on the order of several hundred milliseconds rather than several minutes, and barely registered a blip on the CPU/DTU. I've never dealt with a table with so many FKs like this, and generally not paid attention to indexes on FK columns, but I will definitely pay more attention to these in the future. May 2, 2018 at 16:40
1

If you have no option to disable FK or take the database offline you could delete in batches with a wait (sleep) the keep the cpu down. It will take longer but that might not be a problem.

1
  • I've considered this, but I was concerned that since all of this is inside of a transaction, that might cause locks/waits on other things. Apr 27, 2018 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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