SQL SERVER 2019 – Performance Issues After Upgrading from SQL Server 2012

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.

SQL SERVER 2019 - Performance Issues After Upgrading from SQL Server 2012 AfterUpgrading-800x413

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)

, , ,
Previous Post
SQL SERVER – Performance Comparison – BETWEEN, IN and Operators
Next Post
SQL SERVER – Find Oldest Updated Statistics – Outdated Statistics

Related Posts

4 Comments. Leave new

  • Could you have just kept the compat level at 2012 and gotten the same result?

    Reply
    • 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.

      Reply
  • 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.

    Reply

Leave a Reply

Menu