1

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!

Improve this question

1 Answer 1

Reset to default
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'))

Improve this answer
5
  • 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?
    – Brendan Gooden
    Commented Jul 2, 2020 at 0:21
  • @BrendanGooden as I said I would start from reviewing actual execution plans :) Could you please add them to your description above?
    – NikitaSerbskiy
    Commented Jul 2, 2020 at 0:26
  • Yes let me get them for you
    – Brendan Gooden
    Commented Jul 2, 2020 at 0:46
  • i have updated the description with the actual plans
    – Brendan Gooden
    Commented Jul 2, 2020 at 0:57
  • @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'
    – NikitaSerbskiy
    Commented Jul 2, 2020 at 9:14

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.