Chapter 9: The Query Store

This chapter covers the Query Store, a feature introduced with SQL Server 2016 that allows you to troubleshoot queries and execution plan-related issues and monitor performance history. The Query Store collects information about queries, plans, and runtime statistics, helping you pinpoint performance differences due to changes in execution plans.

The Query Store has been improved with every release since then and, new with SQL Server 2022, allows you to enable some of the new intelligent query processing features. For example, features such as memory grant feedback, cardinality estimation feedback, and degree of parallelism feedback use the Query Store to persist their information on queries and plans.

New with SQL Server 2022, the Query Store can also be enabled on secondary replicas used by Always On availability groups. This can help in scenarios where you want to troubleshoot query performance on read-only workloads running on secondary replicas...

Using the Query Store

Configuring the Query Store is straightforward – we will enable it a few times in Chapter 10, so that we can work with some of the new intelligent query processing features. In this section, we will configure it. You may want to start with a fresh copy of the AdventureWorks2019 database. In SQL Server Management Studio, right-click the AdventureWorks database, select Properties, and then select Query Store. You will see something similar to the following:

Figure 9.1 – Query Store configuration

To enable the Query Store, you must change Operation Mode (Requested) to Read write. If you click the Script button at the top of the Database Properties window, the following code will be generated:

USE master

GO

ALTER DATABASE AdventureWorks2019 SET QUERY_STORE = ON

GO

ALTER DATABASE AdventureWorks2019 SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

Click OK or run the produced code. In addition, you can use the same...

Querying the Query Store

So far, we have only been using the Query Store user interface directly in SQL Server Management Studio. It won’t be long after you start working with the Query Store that you will need to query the Query Store tables directly. As a quick introduction on how to access such tables, I will cover two examples here. For more details, please refer to the SQL Server documentation.

One of my favorites is the following query to collect queries that have timed out or finished because of an error. But first, let’s create at least one example. Run the following query. Since it is running an expensive CROSS JOIN, it will take too long to finish. Cancel the query after running it for a few seconds:

SELECT * FROM Sales.SalesOrderDetail sod1 CROSS JOIN Sales.SalesOrderDetail sod2

Now, you can run the following report to list all the queries that timed out or finished because of an error:

SELECT rs.avg_logical_io_reads, qt.query_sql_text,

 q...

Summary

This chapter covered the Query Store, a query performance troubleshooting feature that collects information about queries, plans, and runtime statistics and can be used to find performance differences due to execution plan changes.

New with SQL Server 2022, the Query Store can be used to implement query hints and can be enabled on secondary replicas. Query store hints can be used to change the behavior of a query without the need to change the query’s text. The Query Store can be enabled on secondary replicas used by Always On availability groups. This can help in scenarios where you want to troubleshoot query performance on read-only workloads running on such secondary replicas.

Finally, as covered in the next chapter, the Query Store plays a very important role in the new intelligent query processing features that are available. This is because it is required by the memory grant feedback, cardinality estimation feedback, and degree of parallelism feedback features...