High CPU Usage on certain query when changing DB Compatibility Level

Question

We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.

We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.

We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run vs about 2 seconds on the old compatibility level)

enter image description here

I did pull the execution plan for each of these

CompatibilityLvl100 Plan

CompatibilityLvl130 Plan

I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.

I also executed this query to get some more details about the running query, I have uploaded the results as a comparison to a Google Sheet here.

One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32. But I still couldn't figure out what was causing the high CPU usage.

Be glad of anyone's help or direction of something I may have missed!

Answer 1

On higher compatibility level SQL Server starts to use new cardinality estimator which leads to different execution plan. Sometimes this new plan may be less efficient but it doesn't mean that new cardinality estimator works worse. Usually it means you need to review your query and probably adjust your indexes or statistics because probably your original plan was far away from optimal too. You attached estimated execution plans to question description. To find out the root reason of the issue you need to compare actual plans.

You can use traceflag or hint to force SQL Server to use old cardinality estimator for your query: OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

• Brendan Gooden: thanks v much, the force legacy option did the trick. My question is how would I go about improving the query to work with the new cardinality esimator?
• NikitaSerbskiy: @BrendanGooden as I said I would start from reviewing actual execution plans :) Could you please add them to your description above?
• Brendan Gooden: Yes let me get them for you
• Brendan Gooden: i have updated the description with the actual plans
• NikitaSerbskiy: @BrendanGooden I would try to add index ON InventoryItem (Status, ReplacedBy_C). Depending on your data distribution and other queries you can try different ones - (ReplacedBy_C, Status) or even filtered: (ReplacedBy_C) WHERE Status = N'A'