I just finished a call with the client of Comprehensive Database Performance Health Check, who upgraded their SQL Server from 2012 to SQL Server 2019 and started to face SQL Server Performance Regression (poor performing SQL Server). They reached out to me to help them fix their SQL Server Performance issues after upgrading.
Performance Issues After Upgrading
When we upgrade to the latest SQL Server version, we usually expect better performance as our hardware is improved, our SQL Server has the latest algorithm and base OS also contains industry improvements. Trust me, SQL Server 2019 is a very robust and solid product and I have pretty good experience with this over many of the earlier versions of the SQL Server.
However, there have been few scenarios when the upgrade happens the performance is poor. There can be multiple reasons why your performance may be bad after upgrading to the latest version and it is not possible to discuss them without doing a proper investigation of SQL Server.
My client had a very urgent situation where they can’t wait for the complete investigation and they wanted to get their server up and running immediately. They asked me if I know any workaround where their server runs and I can investigate behind the scene the root cause of the slowness.
I was able to restore their SQL Server 2019’s performance at the level of SQL Server 2012 by enabling the legacy cardinality ON.
Legacy Cardinality Estimation On
Here is the blog post where I explain what it is and how you can enable the Legacy Cardinality Estimation ON: SQL SERVER – Enabling Older Legacy Cardinality Estimation.
Here is the script for the enabling the setting:
USE [YourDB] GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO
Once we changed the settings we were able to get the server up and running. After that, we investigated their server properly and found a few queries and indexes which were the key reasons for the regressions. Once we fixed that we were able to get turn off the legacy cardinality estimation setting.
Well, I recommend these settings to be used in the fire fighting situation. Do not turn it on without a proper understanding of its impact on your production server. It would be a good idea to first test it out on your development server before you consider turning it on the production system.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Could you have just kept the compat level at 2012 and gotten the same result?
In that case, SQL Server 2019 features will be disabled. We just want our query to use a different algorithm (older cardinality) but want all the good stuff of SQL Server 2019.
Thanks for the clarification. Do you have a preferred build of SQL 2019 like CU4/5/6 or 8. My client is about to do what your client did and go from 2012 to 2019. I was suggesting keeping the compat level at 20-12 to begin with and then move it up.
That is the reasonable suggestion.
We recently migrated from SQL 2017 to 2019, and have had numerous instances of complex queries that used to perform totally fine (~1-2 seconds) that randomly start performing very poorly (many seconds, sometimes minutes, to the point where they are unusable). Often these queries involve sub-queries / CTEs. Sometimes, adding the query hint to use the old CE estimator helps and returns them to their former glory and good performance. Sometimes I’ve had to re-write the query into multiple statements using temp tables in order to make it usable at all.
I’ve been reluctant to turn on the old CE for the entire database because I’m afraid of what other things that might hurt.
I do all manner of nightly reindexing and statistics updates – typically, statistics are the thing the execution plan suggests could be out of whack and why the query is performing slowly. In general, forcing a night statistics update has helped somewhat, but is not a magic bullet.
Furthermore, our test server (same Windows and SQL versions including CUs) often performs fine, even with a relatively fresh copy of the database from the live server.
I’ve tried to keep up to date with all the 2019 CUs – and in fact, one released last fall helped performance tremendously.
It’s super frustrating to have these performance issues cropping up out of the blue on production servers when they don’t happen on our test server with similar or equal amounts of data (though obviously less users and less day-to-day data modifications). I’ve been coding SQL since 2000 and I’ve never such a difficult time upgrading from one version to another right above it.
Anyways, rant over. If anyone else has any magic bullets I’d love to know. Meanwhile, I just keep hitting one query at a time as the issues arise.
David,
We recently expanded our system and converted to SQL 2019 from 2016.
Every key point and problem you mentioned has also occurred in our environment.
So We’re not alone…..
Any constructive feedback and directions would be muchly appreciated.
Thanks
Hi David,
Please add OPTION(recompile) on the end of the complex queries.
That helped in my case.
Regards,