Managing SQL Server production databases can become a nerve-wracking situation for administrators, especially when your organisation or business can have hundreds of databases that are vital to its existence and commerce.

In many cases, testing performance regression when planning to migrate to a newer version of SQL Server, for even a 2MB database can be a daunting task. Moreover, it is common to come across databases that do not match the version of SQL Server they are installed on. Why, you might think? Often, software vendors may only test and target their applications against certain versions and require databases to be kept at a lower level. As seen in the following example:

Default Compat. Level SQL Server 2016 = 130. | Database Compat. Level: 120 = SQL Server 2014.

The example above suggests the database creator decided the newer features within SQL Server 2016 were not required for the respective database.  In other scenarios, administrators might be worried about functional changes post upgrade or performance regression through poorly chosen plans made by the query optimizer.

Nevertheless, this is where using Query Store and following the workflow below can ease your mind and put you, as a DBA, in control when planning to upgrade database compatibility levels to match the default SQL Server version.

Query Store Workload

1. First, upgrade to the version of SQL Server you wish to migrate/upgrade to. Upgrading to Server 2016(13.0) and beyond, will not provide the latest enhancements to the query optimizer when keeping the old compatibility level but enables the query store feature to be utilised in the next step.
   
   
2. Enable the Query Store feature – this will start capturing workloads against the database. Remember: this is a per-database option, and not system-wide. If you are unsure about which parameters to use or define when enabling Query Store, just select the defaults for now.
   
   
3. With the original compatibility level still enabled, allow query plans or other workload on the server to be captured over time, by using the enabled feature. This will capture executed queries and their plans; giving us an insight into how our workload performed before adjusting the compatibility levels.
   
   
4. Now that you have at least two weeks' of captured workload before the change, upgrade the database to the latest compatibility level you desire. This is where SQL Server and the query optimizer may create new “hopefully good” plans; so, it is important to keep a close eye for any queries which have regressed after making the change. This can be achieved via two methods
   
      A. View the Query Store Regressed Queries Report in Management Studio
   
   

     B. Querying the data captured by Query Store whilst setting a SQL Agent job to monitor and alert.

5. Once you have later identified queries which have regressed in performance, you have the following options below:

• Refactor the query to make it more maintainable and efficient; this allows potential bugs to be diagnosed early on, within development and can improve overall query execution.
• Force the query to use the 'last known good plan' (captured before the upgrade, in step 3) – we will discuss this in further depth, in another upcoming blog post.
• Update statistics at the database level – a good indication to check when statistics were last updated is to query the Data Management Function (DMF) [sys].[dm_db_stats_properties].
• Revert to the previous compatibility level – this tends to be a last resort when plans fail to be forced or if performance is still an ongoing issue.
• Introduced in SQL Server 2016 SP1, “Query Hints” can be used to force the use of the older cardinality estimator and its features whilst retaining newer improvements made to the query optimizer.
  • Use the database option “LEGACY_CARDINALITY_ESTIMATION” enables the whole database to use the old Cardinality estimator but retains the newer features in the Query Optimizer.
  • Query hint “LEGACY_CARDINALITY_ESTIMATION” allows the administrator to force a specific query to use the old cardinality estimator whilst other improvements are also kept in the Query Optimizer.

All in all, enabling Query Store is a fantastic feature first introduced in SQL Server 2016 and should be explored in more detail if you have frequent worries about plan/query regression of any kind in SQL Server. Although we did not discuss the huge array of benefits Query Store brings to performance tuning and optimal plan execution, we suggest researching and find out more by enabling it yourself (in a test environment first!).