Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) as it will be less brittle. Can you show the query plan without forcing?
Mar 7 at 9:44
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