Why does query run quickly in SQL Server 2016 but stalls out in 2019?

Question

We recently upgraded one of our servers from SQL Server 2016 to 2019 CU18. This is one of our PeopleSoft servers. Mostly things run fine, but there are a couple of particular queries that have just ground to a halt. When I look at their query plans, the plans have gone from thousands of rows to tens of millions. Run time went from 10 minutes to now they just don't finish, even after letting them run for hours. Using SolarWinds DPA, we are seeing the queries in question wait for 5-10 minutes at a time for resources (cpu, memory) to become available. Also, tempdb has completely filled its drive, although we're not sure if these queries are to blame for that.

We've tried turning on the legacy cardinality estimator as well as the query optimizer fixes, but the only thing that seems to help is to turn the legacy cardinality estimator and the query optimizer fixes OFF and run in SQL Server 2016 mode.

The query is one that is auto-generated by PeopleSoft, so it is very complex - views constructed on views constructed on views - and we were wondering if there are any specific things we need to look for as we go through and try to tune these queries to run efficiently in 2019.

Answer 1

Microsoft makes changes to the optimizer in every release, and the intention is that these changes will improve performance. And - in many cases they do. Optimization is a kind of a guessing game, and every once in a while, the changes backfire and performance degrades.

The good news is that with Query Store this makes thing a little easier. Turn on Query Store. Run in compatibility mode 130 for some time. Then switch to mode 150. Query Store makes it easy to find degraded queries, and furthermore, you can use Query Store force the old plan for degraded queries.

As for what specific changes in SQL 2019 that may be in play here is impossible to say without any knowledge about queries, plans etc.

Answer 2

Hi @Randall S ,
Yes, set the compatibility mode as SQL Server 2016(130) is a very good choice.

And you can also use trace flag 9476 to force the New CE to use the Simple Containment assumption instead of the default Base Containment assumption. (https://learn.microsoft.com/en-us/troubleshoot/sql/performance/cardinality-estimator-degrades-query-performance)

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"