Query Store in action

In this section, you will see how Query Store collects information about queries and query plans and how it can identify and fix regressed queries. We will demonstrate how Query Store supports and facilitates an upgrade to SQL Server 2017.

In this exercise, you will use the WideWorldImporters sample database. To simulate having created that database with SQL Server 2012, set the compatibility level to 110:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 110;

Now, you will enable and configure Query Store for the database. It will accompany you in your migration adventures. The following statement enables and configures Query Store for the sample database:

ALTER DATABASE WideWorldImporters
SET QUERY_STORE = ON   
    (  
      OPERATION_MODE = READ_WRITE,  
      INTERVAL_LENGTH_MINUTES = 1   
    );  

You can check the status and configured parameters of your Query Store by using the following view:

SELECT * FROM sys.database_query_store_options; 

The following...