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 screenshots show the Query Store configuration, created by the previous command:

As you noticed, in this example you have used a minimal value (one minute) for the parameter INTERVAL_LENGTH_MINUTES. This is done for demonstration purposes; you want to see collected data immediately. On the production system, this action can generate a lot of runtime statistics rows and could mean, for instance, that data is available for the last few hours only.

After you have enabled and configured Query Store, it immediately starts to collect information about executed queries.