The main use case, and most probably the one that triggered introducing of this feature, is upgrading to a new SQL Server version. An upgrade is never a trivial action, and it brings improvements (that's the reason why we upgrade, right?), but sometimes also brings regressions. These regressions are not always predictable and it happens often that companies do not perform a full upgrade to the latest SQL Server version, but leave the most important or most volatile database in the old compatibility mode. This means execution plans will use the logic, rules, and algorithms from the previous database version. By taking this approach, you can reduce the risk of performance regression, but you will not be able to use some of the new features because they are available only in the latest compatibility mode. In my experience, after changes in the Cardinality Estimator in SQL Server 2014, more than 50% of companies did not upgrade all large and volatile databases to its compatibility mode 120 because of significant regressions. Most of the queries perform well, but some of them got different, sub-optimal execution plans and fixing them on production systems would be too expensive and risky.

Query Store can help you to perform upgrades without worrying about issues with different plans in the new version. It can easily and quickly identify issues with execution plans and offer you an option to force the old plan in case of regression, without a big impact to the production workload.

A typical scenario for SQL Server version upgrade follows:

In all these cases, Query Store can help you to identify issues after the actions and to fix problems caused by changed execution plans very quickly, efficiently, and without risking business applications. A typical scenario is:

You can use Query Store to identify ad hoc workloads, which are typically characterized by a relatively large number of different queries executed very rarely, and usually only once. Use the following query to identify all queries that are executed exactly once:

SELECT p.query_id 
FROM sys.query_store_plan p 
INNER JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id 
GROUP BY p.query_id 
HAVING SUM(s.count_executions) = 1; 

In the section, Query Store in Action, you saw that Query Store does not capture runtime statistics only for successfully executed queries. When query execution is aborted by the caller or end with an exception, this info is not stored to server cache, but Query Store collects that info too. This can help you to easily identify queries with an incomplete execution process.

To see an example, you first need to clean-up info that was captured in the previous sections of this chapter:

ALTER DATABASE Mila SET QUERY_STORE CLEAR ALL; 
ALTER DATABASE Mila SET QUERY_STORE = OFF; 
ALTER DATABASE Mila   
SET QUERY_STORE = ON    
    (   
      OPERATION_MODE = READ_WRITE    
    , DATA_FLUSH_INTERVAL_SECONDS = 2000       
    , INTERVAL_LENGTH_MINUTES = 1    
    );  

You should also ensure that the latest compatibility mode is applied:

ALTER DATABASE Mila SET COMPATIBILITY_LEVEL = 130; 

Now, you can execute the same query as you did in the section Query Store in Action:

SELECT * FROM dbo.Orders WHERE status IN (0,2); 
GO 100 

You should execute the query, then click the cancel executing query button in the SQL Server management studio, then click again to execute it in order to simulate the execution and query abortion.

In addition to this you should execute the following query, too:

SELECT Status/ (SELECT COUNT(*) FROM dbo.Orders WHERE Status IN (0, 2)) FROM dbo.Orders WHERE id = 1; 

This query will not be successfully executed; it will raise a Divide by zero exception. Now, you can check what Query Store has captured. To do this run the following code:

SELECT * FROM sys.query_store_runtime_stats;  

In the following screenshot you can see two entries for your initial query (regular executed and aborted) and also an entry for the query with the Divide by zero exception:

Figure 9-23. Identifying unfinished queries by using Query Store

As you can see, by using Query Store you can easily identify started but not executed queries in your database.