Enabling and Configuring Query Store

The Query Store can also be configured using SSMS. To do this, drill through Databases in Object Explorer and select Properties from the context menu of the database, for which you wish to configure the Query Store. In the Properties page, you can select the Query Store tab, which is illustrated in Figure 22-1.

Working with Query Store Data

Query Store Reports

Now we have run some queries, the Query Store will have captured data about those queries. We can view this data through a series of SSMS reports. The standard reports are

• View Regressed Queries

• View Overall Resource Consumption

• View Queries with Forced Plans

• View Queries with High Variation

• Query Wait Statistics

• View Tracked Queries

These reports can be accessed by drilling through Databases ➤ [Database Name] and then selecting the appropriate report from the context menu of the Query Store node. In this chapter, we will examine some of the reports that I find most useful, but I encourage you to experiment with all of them.

Tip

Because of the nature of the reports, when you first set up Query Store, there will be very little data. The data points will grow as time moves forward after Query Store is enabled.

The View Overall Resource Consumption report is illustrated in Figure 22-2. This report consists of four charts, representing execution duration, execution count, CPU Time, and Logical Reads, aggregated over time intervals.

Hovering your mouse over a bar will cause a box with further details to be displayed, as you can see in Figure 22-3.

Clicking the Standard Grid button will replace the bar charts with a grid, with one row per time interval, showing aggregated runtime statistics. This grid can be copied to Excel for further analysis. The grid is illustrated in Figure 22-4.

Tip

You will notice that the execution count is far higher than the number of queries we have run. This is because SQL Server runs queries in the background, as part of many different processes, including queries that are run when you navigate SSMS.

Figure 22-5 illustrates the Top Resource Consumers report. The top-left corner of this report shows a bar chart, representing the most resource-intensive queries. The Metric drop-down defaults to Duration, but many different metrics can be selected, including degree of parallelism (DOP), CPU Time, Row Count, Wait Time even CLR Time, or Log Memory Used, to name but a few.

The top-right corner of the report shows a scatter chart, detailing resource utilization and execution time of each plan. This is helpful when a query has been executed using multiple plans, as you can easily assess the most efficient plans for a query.

The bottom half of the plan shows the accrual execution plan that was used. As with any other graphical query plan representation in SSMS, hovering over a physical operator will show cost information for that operator.

The front page of the Query Wait Statistics report can be seen in Figure 22-6. The bar chart at the top of the screen shows a summary of resources that the highest cumulative wait times have been against.

Each bar in the bar chart is clickable. For example, if I were to click the CPU bar, then the drill-through report in Figure 22-7 would be displayed.

By default, the top half of the grid will show the most expensive queries (labelled by Plan ID), based on Total Wait Time, but as you can see, the Based on drop-down list can be changed to base the results on average, min, max wait time, or the standard deviation.

The lower half of the report shows the graphical representation of the Query Plan that has been selected in the top half of the report.

Resolving Issues with Query Store

Figure 22-8 illustrates the Regressed Query report. This report displays queries, the impact of which, in terms of duration, or execution count have increased over time. These queries are illustrated with a bar chart in the top left of the report. The top right of the report illustrates the plans that have been used to execute the query, with their associated duration. At the bottom of the report, we can see the actual execution plan for the current selection of query, and plan in the top half of the screen.

In this particular example, we can see that the query has run using two distinct plans and that the plan with an ID of 168 took significantly less time to execute than the plan with an ID of 110. Therefore, with the more efficient plan selected, we can use the Force Plan button, in the middle of the report, to ensure that the more efficient plan is always used to execute this query. Once we have used this feature, the Unforce Plan button will become active, allowing us to undo our action, if required.

Query Store Hints

Unlike many other languages, T-SQL is a descriptive and declarative language. This means, when writing a query, that instead of telling the database engine exactly what to do, you describe the results that you want to receive and the query optimizer figures out the best way to deliver those results. The query optimizer is incredibly good at deciding the best way to do something, but if it does make a mistake, it can have a negative impact on query performance.

This is where query hints come in. A query hint allows you to force the optimizer down a specific route. For example, you might tell it that it should recompile the query every time it runs. Alternatively, you may tell it to always use a certain type of physical join operation, or to always use a specific MAXDOP.

The challenge with query hints is that you are not always able to modify the code, which means that you cannot add a query hint to the query. This may be because the code belongs to a third party and support agreements do not allow for code changes, or it could be because there is an urgent performance issue and code release procedures are simply too cumbersome to resolve the issue in a timely manner.

To address this, SQL Server introduced plan guides, where you could specify a statement and one or more query hints that should always be applied, whenever that query runs. You could even use plan freezing, where you specified the USE PLAN hint, and then pass a specific query plan that you wanted to be used. The problem with plan guides is that they are far from user friendly and generally required a highly experienced DBA to create them. SQL Server 2022 resolves this issue with query store hints, which give DBAs the opportunity to specify query hints in a much more friendly way.

If you are trying to address a performance issue with a query and need to specify a query hint, then you can use query store functionality to add a query hint. For example, imagine that your performance troubleshooting has uncovered that a Nested Loops join operator is not the ideal choice for the plan illustrated in Figure 22-9.

Because the optimizer is so good, I always advise working with the optimizer, rather than against it. Therefore, in this case, instead of using a query hint to force the optimizer to use a Merge Join, which is the natural inclination of many DBAs, I would advise using query hints to tell the optimizer to use either a Merge Join operation or a Hash Match operation. This translates to “Nested Loops is wrong. Use anything except this.” The optimizer can then select the best option from its remaining choices.

A nice safety feature of query store hints is that it will ignore any hint that stops it from running, as opposed to blocking the query. Therefore, you cannot inadvertently break a query. The query store hints will also override any query hints or plan guides that conflict with it.

Summary

The Query Store is a very powerful feature of SQL Server, which allows DBAs to monitor the performance of specific queries and their plans. Because the data is flushed to disk, this means that the data persists, even after an instance is restarted.

Six standard reports are available, which allow DBAs to view information such as regressed queries, the most resource-intensive queries, or even wait statistics, by query.

As well as viewing problematic queries, DBAs can also force the most performant plan to be used. This can be achieved through the interactive reports, or via automation, using T-SQL. The automated approach would examine the Query Store data through the catalog views and then pass the relevant data to the parameters of system stored procedures.

SQL Server gives DBAs granular control over which queries are captured by Query Store. This is implemented with custom capture policies, where details such as execution count and execution time can be used to capture only the queries with the highest performance impact. This not only saves disk space but also avoids “noise” when working with the captured data.

In SQL Server 2022, query store has been enhanced to include query store hints, which make it easy and safe for a DBA to use query hints to resolve performance issues, without the need to edit the code. SQL Server 2022 also introduces the ability to use query store on Secondary Availability Replicas, but at the time of writing, this feature was in preview, and not supported for production workloads. Further discussion of Availability Groups can be found in Chapter 15.