With the impending “triple witching hour” of SQL Server 2008 and SQL Server 2008 R2 falling out of extended support and SQL Server 2014 falling out of mainstream support from Microsoft on July 9, 2019, combined with new and impending releases of Windows Server 2019 and SQL Server 2019; an increasing number of organizations are planning and implementing upgrades from legacy versions of SQL Server to a “modern” version of SQL Server. I define a modern version of SQL Server as SQL Server 2016 or newer.

For several reasons, upgrading to modern versions of SQL Server can be more challenging than it was when you upgraded to SQL Server 2012 or older. SQL Server 2014 and newer use the database compatibility level to control an increasing number of performance-related behaviors that many database professionals are not fully aware of. 

SQL Server 2014 Changes

In SQL Server 2014, when you use database compatibility level 120, you will be using (by default) what was called the “new” cardinality estimator, which received a major upgrade in SQL Server 2014. You had the ability to control which cardinality estimator was used with an instance-level trace flag or with query-level hints. Joe Sack did a masterful job of explaining how this worked in his 2014 whitepaper “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator”. Since then, Microsoft has added both more control and more complexity, which you need to be aware of in order to increase your chances of a smooth and successful upgrade without performance regressions.

SQL Server 2016 Changes

In SQL Server 2016, Microsoft began tying more performance-related changes to the native database compatibility level of 130. These included enabling the effects of global trace flags 1117, 1118, and 2371, and enabling trace flag 4199 (for query optimizer hotfixes as of SQL Server 2016 RTM). SQL Server 2016 also introduced database scoped configuration options, which give you the ability to control some behaviors that were formerly configured at the instance level, using an ALTER DATABASE SCOPED CONFIGURATION command. In SQL Server 2016, these options included MAXDOP, LEGACY_CARDINALITY ESTIMATION, PARAMETER_SNIFFING, and QUERY_OPTIMIZER_HOTFIXES.

The QUERY_OPTIMIZER_HOTFIXES option is equivalent to trace flag 4199 at the database level. If you do enable TF 4199 or enable QUERY_OPTIMIZER_HOTFIXES, you will also get all of the query optimizer hotfixes that were released after SQL Server 2016 RTM. SQL Server 2016 SP1 also introduced the USE HINT query hints that are easier to use, understand, and remember than the older QUERYTRACEON query hints. This gives you more fine-grained control over query optimizer behavior that is related to database compatibility level and the version of the cardinality estimator that is being used.

SQL Server 2017 Changes

Microsoft added the new adaptive query processing feature (which is an Enterprise Edition feature) that is enabled by default when you are using database compatibility level 140. Starting with SQL Server 2017 CU10, you can use the USE HINT functionality to control which exact cardinality estimator level is used at the query level by using a query hint.

SQL Server 2019 Changes

SQL Server 2019 has even more performance improvements and behavior changes that are enabled by default when a database is using compatibility mode 150. A very useful example is scalar UDF inlining. Another example is the intelligent query processing feature, which is a superset of the adaptive query processing feature in SQL Server 2017. There are additional USE HINT options that let you disable batch mode or disable adaptive memory grant feedback at the query level. There are also sixteen new database scoped configuration options (as of SQL Server 2019 CTP 2.2) that let you control many existing and new behaviors at the database level. 

Avoiding SQL Server Upgrade Performance Issues

You really want to avoid a haphazard “blind” migration where you are blissfully unaware of how these configuration options interact and how your workload will react to these changes. Changing the database compatibility level to an appropriate version and using the appropriate database scoped configuration options, along with appropriate query hints where absolutely necessary, is extremely important with modern versions of SQL Server. It is also very important to do proper performance testing with these different options, preferably by using the free Microsoft Database Experimentation Assistant (DEA).

Microsoft’s recommended upgrade process is to upgrade to the latest SQL Server version, but keep the source database compatibility level. Then, enable Query Store on each database and collect baseline data on the workload. Next, you set the database compatibility level to the latest version, and then use Query Store to fix performance regressions by forcing the last known good plan.

Query Tuning Assistant

Microsoft is shipping a new tool called Query Tuning Assistant (QTA) in SSMS 18.0. QTA can guide you through the recommended database compatibility level upgrade process in a wizard-fashion, collecting the baseline workload in Query Store, changing the database compatibility level, and then comparing performance with the post-upgrade workload collection. At the end of this process, if performance regressions are detected, rather than just moving back to the previously known good plan, the QTA will actually suggest hint-based improvements that can be deployed for individual queries (using plan guides), without having to necessarily move back to the legacy CE. It will also give you some ideas (indirectly, by example) for how you can modify problematic queries that have CE-related regression issues, when you have that option.

Conclusion

Migrating to a modern version of SQL Server (meaning SQL Server 2016 or newer) is significantly more complicated than it was with legacy versions of SQL Server. Because of the changes associated with the various database compatibility levels and various cardinality estimator versions, it is actually very important to put some thought, planning, and actual testing into what database compatibility level you want to use on the new version of SQL Server that you are migrating your existing databases to, along with the other configuration options that are now available.