Upgrading SQL Server–Diagnosing Performance Regressions

One fairly common issue you might encounter when upgrading to SQL Server 2014 or later are performance regressions with some queries. SQL Server 2014 introduced a new cardinality estimator (CE) that is used by the query optimizer by default when you change the compatibility level of a database to 120 or higher.

Most of the time, the new CE works well, and gives better query performance than the legacy CE. Unfortunately, the new CE sometimes leads to dramatically worse query performance for some queries. This particular issue has been the most frequent problem I have experienced and heard of with upgrades to SQL Server 2014 and newer over the past several years. Fortunately, there are a number of things you can do to prevent being surprised by this when you go into production.

First, you need to do a thorough job of testing query performance on the new version of SQL Server, with both cardinality estimators. This should start with manual testing of your more important stored procedures and queries, capturing their execution plans and io statistics. Then, during any functional or load testing, you should be collecting and analyzing diagnostic information using my DMV Diagnostic queries.

Ideally, you should be doing additional testing and analysis with the new Microsoft Database Experimentation Assistant (DEA). Database Experimentation Assistant is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. It can be used to compare performance between SQL Server 2005 and any later version of SQL Server.

If you do run into performance regressions with the new version of SQL Server, the very first thing you should do is read Joe Sack’s seminal whitepaper “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” to understand how the new CE works, and what mitigation options were available with SQL Server 2014.

These include changing the compatibility level to 110 for a particular database to get the legacy CE for the entire database, or using query-level hints to force the legacy or new CE for a particular query, regardless of the database compatibility level. You can also use an instance-level trace flag to use either CE for all databases.

SQL Server 2016 lets you use database-scoped configurations to change which CE will be used for each database, regardless of the database compatibility model. This gives you a lot more flexibility. SQL Server 2016 also has Query Store, which is very helpful for finding and correcting query plan regressions.

SQL Server 2017 adds automatic plan correction which leverages Query Store to try to force the last known good plan for query nplans that have performance regressions.

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.

Here is a link to the complete series about upgrading SQL Server.