In previous sections, you saw how Query Store captures and stores data about queries and their execution plans. It is now time to see how Query Store can help you with migration. You will execute the same queries under different compatibility levels; firstly under 110 (which corresponds to SQL Server 2012) and 140, the compatibility level of SQL Server 2017. This action will simulate what can happen when you migrate a database to SQL Server 2017.

To simulate migration to SQL Server 2017, you will now change the compatibility level of the sample database to 110:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;

Now, execute the same query from the previous section:

SET NOCOUNT ON;
SELECT * FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID= ol.OrderID
WHERE SalespersonPersonID IN (0, 897);
GO 100

This time, the execution will take much longer thane under the old compatibility mode; you will see why later. It is clear that a new plan has been created for the query and it is also clear that the old one was better. After the execution is done, you can check query and plan repositories. Since you already know the query_id for the query, you can check the plan repository to confirm that the plan has been changed under the new compatibility mode, with the help of the following code:

SELECT * FROM sys.query_store_plan WHERE query_id = 1; 

The following screenshot shows two entries in the plan repository. You can also see that two plans have been generated with different compatibility levels: 110 and 140 respectively:

Setting the compatibility level for the sample database to 140 triggers the generation of new execution plans for queries in this database. Most of them will probably be the same as they were before, but some of them will change. You can expect small or big improvements for most of them, but the upgrade to the latest compatibility mode will introduce significant regression for some queries, as in the sample query in this section. In the next section, you will see how Query Store can help you to solve these issues.