Query Store Function and Design

The Query Store has various functionality, but the chief thing it does is to collect aggregate information on the queries running within a given database. In addition to this information collection, it also gathers the execution plans for those queries. Query Store is enabled by default in Azure SQL Database. In SQL Server, you can choose to enable it on a database-by-database basis.

You can also use the Database Properties within SQL Server Management Studio to control Query Store. I’ll cover that in detail later in the chapter.

When Query Store is enabled, the behavior is illustrated in Figure 6-1.

A diagram displays the query optimization process and query execution points to plan store and runtime statistics, respectively, via async arrows.

Query optimization is not affected directly by the Query Store. When a query is submitted to the system, an execution plan gets generated as outlined in Chapter 2. Normally, the plan then gets stored in the plan cache (which we’ll cover in detail in Chapter 7), but plan forcing can change that behavior. I’ll detail how plan forcing changes the standard behavior later in the chapter. Assuming there is no forced plan, after the plan gets stored in the plan cache, an asynchronous process copies the plan to a separate memory area for temporary storage. Then, another asynchronous process writes the plan to the Query Store within the database system tables. These asynchronous processes are meant to reduce the overhead from the Query Store as much as possible.

Query execution then proceeds as normal. When the query completes executing, the runtime metrics such as duration, reads, wait statistics, and more are written to a separate memory space through an asynchronous process. That data is aggregated as it’s stored. Then another asynchronous process ensures that the information gets written to the system tables on your database. The default aggregation interval is 60 minutes, and you can adjust that up or down as appropriate for your system.

The information captured by the Query Store is written into the databases on which it is enabled. The query metrics and the execution plans are kept with the database. They get backed up with the database and will get restored with the database. These are system tables and will be stored on the main drive with all other system tables. If your system was to go offline for some reason, or when failing over, it is possible to lose the Query Store information that is in memory and not yet written to disk. The default interval for writing the information to disk is 15 minutes, and you can control that as well. Since this is aggregate information, the possibility of data loss in this case is not concerning.

When you query the information from Query Store, you get both the in-memory data and the data that has been written to disk. This is automatic, and you don’t have to do anything to make it happen.

Information Collected by Query Store

The core piece of data that drives the Query Store is the individual query itself. Any given query may be part of a stored procedure or a collective batch of queries. That doesn’t matter when it comes to the Query Store. It collects information based on the individual query statement. You can easily match it back to a stored procedure, the object_id is captured, but you can’t connect it back to any batch processes.

There are seven system tables that store the Query Store information:

• sys.query_store_query: The main table for information about the queries within Query Store

• sys.query_store_query_text: The T-SQL code for a given query

• sys.query_store_plan: All the plans for a given query

• sys.query_store_runtime_stats: The aggregated runtime metrics gathered by the Query Store

• sys.query_store_wait_stats: Aggregated wait statistics for each query

• sys.query_store_runtime_stats_interval: The start and stop time for each of the intervals within the Query Store

• sys.database_query_store_options: This is a store showing the various settings within Query Store on a given database

There are a number of reports within Management Studio to enable you to look at Query Store information, but I also write a lot of queries to pull information I want quickly. For example, if you wanted to see information about a particular stored procedure, you could do something like Listing 6-2.

SELECT qsq.query_id,

       qsq.object_id,

       qsqt.query_sql_text,

          qsp.plan_id,

       CAST(qsp.query_plan AS XML) AS QueryPlan

FROM sys.query_store_query AS qsq

    JOIN sys.query_store_query_text AS qsqt

        ON qsq.query_text_id = qsqt.query_text_id

    JOIN sys.query_store_plan AS qsp

        ON qsp.query_id = qsq.query_id

WHERE qsq.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

Listing 6-2

Querying the Query Store for a stored procedure query

While each individual query statement is stored within the sys.query_store_query table, you also get the object_id value, so you can use functions like OBJECT_ID() as I did to identify my stored procedure by name. I also used the CAST command on the query_plan column. This is because Query Store rightly stores this column as NVARCHAR(MAX), not XML. The XML data type has a nesting limit. You can see this in action in the DMVs where you have two places to get execution plans: sys.dm_exec_query_plan and sys.dm_exec_text_query_plan. Query Store wisely eliminates the need for two kinds of storage, again, making it more efficient by design. So if you want to be able to click on the results of the query, as shown in Figure 6-2, you’ll need to use CAST as I did in Listing 6-2.

A table has 5 columns and 3 rows. The column headers are query i d, the object i d, query s g l text, plan i d, and query plan, the first row entry 1, is highlighted.

Here, you can see that I have a stored procedure with one statement, identified as query_id = 1. However, for that one query, there are three different execution plans in the plan_id: 1, 10, and 11.

Something important to take note of here is how the text of the query is stored in the Query Store. Since this statement is part of a stored procedure with parameters, the parameter definition is included with the T-SQL text. This is what the statement looks like within the Query Store (formatting left as is):

(@ReferenceOrderID int)SELECT  p.Name,              p.ProductNumber,              th.ReferenceOrderID      FROM    Production.Product AS p      JOIN    Production.TransactionHistory AS th              ON th.ProductID = p.ProductID      WHERE   th.ReferenceOrderID = @ReferenceOrderID

You can see the parameter definition at the start of the query. The actual stored procedure I’m using is in Listing 6-3.

CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference

(@ReferenceOrderID int)

AS

BEGIN

    SELECT p.Name,

           p.ProductNumber,

           th.ReferenceOrderID

    FROM Production.Product AS p

        JOIN Production.TransactionHistory AS th

            ON th.ProductID = p.ProductID

    WHERE th.ReferenceOrderID = @ReferenceOrderID;

END;

Listing 6-3

Procedure definition for ProductTransactionHistoryByReference

The statement stored in Query Store is different than the statement in the stored procedure because of that parameter definition. This can lead to some minor issues when attempting to find a particular query within Query Store. Listing 6-4 has another interesting example.

SELECT a.AddressID,

       a.AddressLine1

FROM Person.Address AS a

WHERE a.AddressID = 72;

Listing 6-4

Batch process running a simple query

While this is a batch and not a stored procedure, it can still be parameterized through simple parameterization. Luckily, Query Store has a function for dealing with parameterization, sys.fn_stmt_sql_handle_from_sql_stmt. That function retrieves the SQL handle that can be used to identify the query as shown in Listing 6-5.

SELECT qsq.query_id,

       qsq.query_hash,

       qsqt.query_sql_text,

       qsq.query_parameterization_type

FROM sys.query_store_query_text AS qsqt

    JOIN sys.query_store_query AS qsq

        ON qsq.query_text_id = qsqt.query_text_id

    JOIN sys.fn_stmt_sql_handle_from_sql_stmt(

             'SELECT a.AddressID,

       a.AddressLine1

FROM Person.Address AS a

WHERE a.AddressID = 72;',

             2)  AS fsshfss

        ON fsshfss.statement_sql_handle = qsqt.statement_sql_handle;

Listing 6-5

Putting sys.fn_stmt_sql_handle_from_sql_stmt to work

The formatting and white space all have to be the same in order for the function to succeed. The hard-coded value can be different, because it’s going to get replaced anyway, but the other parts of the T-SQL have to be identical. The bad news is sys.fn_stmt_sql_handle_from_sql_stmt only works with automatic parameterization. It won’t help you with prepared statements or stored procedures. To retrieve that information, you will be forced to use the LIKE command when searching for text. This is why I usually just use the object_id or query_hash values to track things down when I can.

Query Runtime Data

Query and the execution plan are great pieces of information to have. However, you also want to see runtime metrics. The runtime metrics are a little different than you would initially expect. First of all, the runtime metrics are matched to a given execution plan, not the query. Since each plan could, and probably will, behave differently, the runtime metrics are captured for the plan. Second, the runtime metrics are aggregated by the runtime interval. The default value for the interval is 60 minutes. This means that for each interval, you’ll have a different set of runtime metrics if your query was run during that interval.

You can easily combine the runtime metrics with the information on the query itself. Because the information is aggregated into discrete intervals, it may be necessary to aggregate the aggregates, meaning sum up or average the accumulated values. While this may seem like a pain, the purpose for the aggregation intervals is extremely helpful. You have the aggregates broken up so that you have more than one point of reference. You can track how a query’s behavior changes over time. Having comparison points is how you can tell if performance is degrading or improving. You get some of the granularity and detail of using Extended Events with the ease of simply querying the cache.

Listing 6-6 shows one example of how you can retrieve metrics for a specific moment in time.

DECLARE @CompareTime DATETIME = '2021-11-28 15:55';

SELECT CAST(qsp.query_plan AS XML),

       qsrs.count_executions,

       qsrs.avg_duration,

       qsrs.stdev_duration,

       qsws.wait_category_desc,

       qsws.avg_query_wait_time_ms,

       qsws.stdev_query_wait_time_ms

FROM sys.query_store_plan AS qsp

    JOIN sys.query_store_runtime_stats AS qsrs

        ON qsrs.plan_id = qsp.plan_id

    JOIN sys.query_store_runtime_stats_interval AS qsrsi

        ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id

    LEFT JOIN sys.query_store_wait_stats AS qsws

        ON qsws.plan_id = qsrs.plan_id

           AND qsws.plan_id = qsrs.plan_id

           AND qsws.execution_type = qsrs.execution_type

           AND qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id

WHERE qsp.plan_id = 329

      AND @CompareTime BETWEEN qsrsi.start_time

                       AND     qsrsi.end_time;

Listing 6-6

Retrieving runtime information from the Query Store

The query in Listing 6-6 pulls back both the average duration and the standard deviation for that average in order to better understand how accurate the average is. The results for my query are shown in Figure 6-3.

A table with 7 columns and 1 row displays the runtime metrics and wait for statistics for a 1-time interval plan.

In addition to associating the performance with a given plan, the execution context is also taken into account. A query from a batch and the same query in a stored procedure may have different behaviors.

You can combine all the metrics for a given query using Listing 6-7.

WITH QSAggregate

AS (SELECT qsrs.plan_id,

           SUM(qsrs.count_executions) AS CountExecutions,

           AVG(qsrs.avg_duration) AS AvgDuration,

           AVG(qsrs.stdev_duration) AS StDevDuration,

           qsws.wait_category_desc,

           AVG(qsws.avg_query_wait_time_ms) AS AvgQueryWaitTime,

           AVG(qsws.stdev_query_wait_time_ms) AS StDevQueryWaitTime

    FROM sys.query_store_runtime_stats AS qsrs

        LEFT JOIN sys.query_store_wait_stats AS qsws

            ON qsws.plan_id = qsrs.plan_id

               AND qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id

    GROUP BY qsrs.plan_id,

             qsws.wait_category_desc)

SELECT CAST(qsp.query_plan AS XML),

       qsa.*

FROM sys.query_store_plan AS qsp

    JOIN QSAggregate AS qsa

        ON qsa.plan_id = qsp.plan_id

WHERE qsp.plan_id = 329;

Listing 6-7

Taking an average of all Query Store runtime metrics for a query

I use a LEFT JOIN here because you won’t always have wait statistics since the Query Store only captures waits longer than 1ms. This will retrieve all performance for the plan_id specified, regardless of the interval.

Controlling Query Store

The queries earlier in the chapter showed how to get both the plan_id and query_id values by searching for queries or objects using the information stored within Query Store.

If you make changes to the settings within Query Store, they take effect immediately. There are no reboots or resets of any kind required. The default values are adequate for many systems. However, there is one set of values you may want to adjust on your systems, and that’s the Query Store Capture Mode.

Capture Mode

Prior to SQL Server 2019, you had only three options for how the Query Store captured queries. The default is All, meaning it will capture all queries. You also have the option of setting the capture mode to None. This means that the Query Store remains enabled, so you can do things like plan forcing (explained later in this chapter), but you stop capturing new data within Query Store. Finally, you had Auto as an option. The old behavior here meant that Query Store would only capture queries that had been executed three times or had run longer than one second. This helped reduce some of the overhead of Query Store and helped it work well with “Optimize for Ad Hoc”, a setting that helps with plan cache memory management (we’ll discuss this in Chapter 7).

In Azure SQL Database and SQL Server 2019, you have a completely different way to control how Query Store captures queries. Figure 6-4 shows the database properties for Query Store within SSMS.

A webpage displays the general, monitoring, query store retention, query store capture policy, and query store capture mode sections. Under the query store retention, the query store capture mode is set to custom.

I’ve highlighted two boxes in Figure 6-4. The first, on top, shows where you change the Query Store Capture Mode. It’s set to Custom, the new mechanism for controlling Query Store. When you select Custom from the drop-down menu, it enables the second set of properties I have highlighted there in Figure 6-4. You have four settings you can control:

• Execution Count: The number of times a query must run before you capture it within Query Store

• Stale Threshold: The time period in which the query must meet the other criteria you’re setting

• Total Compile CPU Time (ms): The total amount of CPU used during the time period before it gets captured

• Total Execution CPU Time (ms): As with compile CPU time, but measuring the execution CPU time

Obviously, some information is captured on these queries in order for the Query Store to know when it must capture all the information it would normally capture. However, using these filters allows you to take very direct control over how much information is captured by the Query Store.

Just remember, all these settings for Query Store are on a database level only. If you decide to set a particular value and you want it to be a standard across the system, you’ll have to set it on each database on which you’re running Query Store.

Query Store Reporting

In addition to using T-SQL to consume the information captured by Query Store, there are a number of customizable reports built into SSMS. These reports provide a very full set of functionalities, making consuming these reports flexible and easy. On any database that has Query Store enabled, you’ll see a new folder, Query Store, and inside of it all the reports as shown in Figure 6-5.

A window displays the Adventure Works database. Under it is the Query store folder with 7 databases.

We don’t have the space to cover every report in detail. I’m going to focus on the report you’re most likely to make use of, Top Resource Consuming Queries. Double-click the report to open it, and you’ll see something similar to Figure 6-6.

A window displays 2 graphs indicate the top 25 resource consumers report from the query store.

The report consists of three different pieces of data. On the top left is a listing of the most costly reports. On the top right are the execution plans for the query that is currently highlighted on the left. At the bottom is the execution plan.

Hover the mouse over the queries on the left, and you’ll get information about the query in question as you can see in Figure 6-7.

A list displays the properties of the query from the report consists of the following information, query i d, object i d, object name, total duration, execution count, and plan count.

You can see important pieces of information such as the execution count, the query itself, and the number of plans associated.

You can also hover over the plans on the right side of Figure 6-6, and you’ll get information about that execution plan. Something like Figure 6-8.

A list displays 12 properties of the given execution plan, the plan i d, execution type, plan forced, interval start, interval end, execution count, total duration, average duration, minimum duration, maximum duration, s t d dev duration, and variation duration.

Each of those plans represents different moments in time between plan compiles and recompiles as well as plan changes. You can see the performance metrics for the plan in question including such things as the execution count for the plan, as opposed to the one for the query.

On the top right of the report is a button labeled Configure. Clicking on that brings up the full configuration screen for the report as shown in Figure 6-9.

A window for Configure Top Resource Consumption has the following sections, Resource Consumption Criteria, Time Interval, Return, and Filters.

You can pick any of those metrics to get a whole new report. You can also decide between various aggregations for those metrics. Below that, you can pick the time interval, including full customization. The number of queries can be controlled by setting the Return value. Finally, you can filter the data based on the number of execution plans per query.

The report has one last piece of functionality worth mentioning. On both the plan runtime metrics and the plan itself, there is a button that lets you force or unforce a given plan. Figure 6-10 shows the buttons from the execution plan.

An image displays 2 buttons, named Force Plan and Unforce Plan.

We’re going to cover plan forcing in detail in the next section.

Plan Forcing

The majority of the functionality in Query Store is very much focused around capturing the plans, runtime metrics, and wait statistics of the queries on the database. There is, however, one additional piece of functionality, plan forcing. Plan forcing is simply the ability to mark an execution plan as the preferred plan. The optimization process occurs as per normal. Then, a check is made to see if there is a forced plan. If there is one, then that plan will be used, if it’s a valid execution plan.

The main point of forcing plans is the ability to ensure consistent behavior from your queries. A recompile of a query could lead to changes in behavior that negatively impact the system. Forcing a plan makes that behavior go away by ensuring one plan is used instead of any other.

Most queries support plan forcing. If there are any that don’t, you will get an error. Only two types of cursors support plan forcing: fast forward and static.

After running this command, any time the query in question gets compiled or recompiled, the plan represented by the ID value, 339, will be used. There is a rare exception where the optimizer can use a different plan after recompiling. The plan will be approximately the same. The actual term used by Microsoft for this event is “a morally equivalent plan.” Again, this is a rare event, but it does happen.

With a forced plan in place, we can take a look at the “Queries With Forced Plans” report, visible in Figure 6-11.

A window displays reports for queries with forced plans. On the right is a graph and below is the diagram for the execution plan.

As you can see, the report looks similar to the Top 25 Resource Consumers shown in Figure 6-6. The behaviors are all largely the same as well. The key difference is that the upper left simply shows a list of all queries with forced plans. Another difference is visible in the list of plans where we can see that one plan has a check mark on it. This is showing the plan that is currently being forced.

I can unforce the plan by ensuring it’s selected and then hitting the appropriate button from Figure 6-10.

Query Store for Upgrades

While general query performance monitoring and tuning may be the day-to-day common use for the Query Store, one of the most powerful purposes behind the tool is its use as a safety net for upgrading SQL Server.

Let’s assume that you are planning to migrate from SQL Server 2012 to SQL Server 2022. Traditionally, you would upgrade your database on a test instance somewhere and then run a battery of tests to ensure that things are going to work well. If you catch and document all the issues, great. Unfortunately, it might require some code rewrites because of some of the changes to the optimizer or the cardinality estimation engine. That could cause delays to the upgrade, or the business might even decide to try to avoid it altogether (a frequent, if poor, choice). That assumes you catch the issues. It’s entirely possible to miss that a particular query has suddenly started behaving poorly because of a change in estimated row counts or something else.

This approach won’t prevent all problems. You still must test your system. However, using Query Store will provide you with mechanisms for dealing with internal changes within SQL Server that affect your query plans and subsequently your performance. You can also use similar processes to apply a Cumulative Update or Service Pack.

Summary

With Query Store, you have one more tool in your toolbox to assist in identifying poorly performing queries. When you need detailed analysis, you’ll still rely on Extended Events. However, when you want fast access to data with the ability to do comparisons between points in time, Query Store has you covered. With the ability to capture multiple execution plans per query so you can see how that changes performance over time, you’ve got even more than you had before. Finally, you can force plans and force hints to get the behavior you want out of your queries.

In the last several chapters, we’ve discussed how execution plans get created, how to read them, and even how to force them through the Query Store. In the next chapter, we discuss how the query plan cache behaves in SQL Server and how it can impact the performance of your systems.