If you have been working with SQL Server long enough, you have probably been involved with a SQL Server 2000 to 2005 upgrade. It was painful for many shops due to the deprecation of some features like DTS and the removal of old-fashioned comma joins with the plus operator. It was worth the pain as SQL Server became a real enterprise worthy database system with that release. There have been six releases of SQL Server since then, with 2019 on the way.

Microsoft has provided tools to help with upgrades such as the Upgrade Advisors and a performance counter to figure out if you are using any deprecated features which might break your application when you upgrade. Theoretically, you figure this out early enough to rewrite any problematic code. As a last resort for some features, you can keep the database in an older compatibility mode to make the database work as if it is on an older server version until the issues are resolved.

Of course, making new versions of SQL Server is not about taking away features, but adding new functionality. In addition to these improvements, the teams have made changes to the optimizer in recent years to squeeze out even better performance. One group of optimization features is called Intelligent Query Processing. These features, such as Table Variable Deferred Compilation and Scalar UDF Inlining, can help solve issues that have been plaguing many of us for years.

In 2014, Microsoft began working on cardinality estimation. This is great news as most queries are positively impacted by the changes. Unfortunately, there are always a handful of queries that perform worse after the change. By changing the compat level, you turn this feature on or off for the entire database, so you can’t avoid running into a few problems.

To get over this issue, Microsoft has added a feature to SQL Server Management Studio 18 (still in preview at the time of this writing). This feature is called the Query Tuning Assistant (QTA). QTA works with Query Store and finds queries that perform worse after a database is switched to a higher compatibility mode. You can then decide to keep an older, better execution plan in place.

The tool has a wizard-like interface to get things set up. You specify which version of SQL Server to test for and how long to collect the baseline workload. Once set up, you’ll launch a dashboard to track progress, upgrade the database, and make decisions about queries. Having this tool available will make upgrading that much easier. This is important because you lose a lot of functionality by keeping databases in lower compat modes.

I’ve been amazed at the advances made to SQL Server over the past few versions and the announcements for this year. I’m glad that Microsoft is providing tools to address some of the pain that customers have experienced when upgrading.