1

After upgrading from SQL Server from 2012 to 2019 we have encountered problems with very simple delete query like:

DELETE [_sales].[OfferDetail]  WHERE [OfferDetailsID]=@1

Operation takes about 15-30 seconds. When analyzing query plan there are nonclustered index scan, table spool and hash match operations that takes most of the time.

How can we optimize that query?

Query plan: https://www.brentozar.com/pastetheplan/?id=HyO1udXJn

0

2 Answers 2

1

It's hard to say without seeing the full table and index definitions, but from the query plan it seems you have a cascading delete, which is doing a big scan and hash join because it is not indexed

Specifically, it looks like you need an index:

SampleTestMethodResult (OfferDetailItemId, SampleTestMethodResultID)

That will get rid of the hash join, but the smaller sorts will remain. It's likely that one single big sort is the issue, hopefully that should be gone. You may want to add other columns into that index as well.

You must index your foreign keys properly, especially if you have cascades. See also SQL-SERVER why my cascading delete gets stuck and Adventures In Foreign Keys 4: How to Index Foreign Keys and The Benefits of Indexing Foreign Keys.

1
  • In that case probably best to just use OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) as it will be less brittle. Can you show the query plan without forcing? Mar 7, 2023 at 9:44
1

I have added the missing index, rebuilt all indexes, updated all statistics with full scan but the problem was still there. When I changed compatibility level to 120 the problem went away.

It looks like I encountered the issue described in Cascading Delete Troubles by Forrest McDaniel.

My plan is to turn on automatic plan correction to force last good plan and in a couple of days I will change the compatibility level to 150. - user87945

Your Answer

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

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