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.

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://darkslategrey-bat-805937.hostingersite.com)