In this article

Monitoring performance by using the Query Store

In this article

The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. You can configure query store using the ALTER DATABASE SET option.

Information in the Query Store

Execution plans for any specific query in SQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. Plans also get evicted from the plan cache due to memory pressure. As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query. This is referred to as plan forcing. Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

Wait stats are another source of information that helps to troubleshoot performance in SQL Server. For a long time, wait statistics were available only on instance level, which made it hard to backtrack it to the actual query. In SQL Server 2017 and Azure SQL Database we added another dimension in Query Store that tracks wait stats.

Common scenarios for using the Query Store feature are:

Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.

Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.

Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

Identify top n queries that are waiting on resources.

Understand wait nature for a particular query or plan.

The query store contains three stores:

a plan store for persisting the execution plan information.

a runtime stats store for persisting the execution statistics information.

a wait stats store for persisting wait statistics information.

The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. To enhance performance, the information is written to the stores asynchronously. To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. The information in these stores is visible by querying the query store catalog views.

The following query returns information about queries and plans in the query store.

Use the Regressed Queries Feature

After enabling the query store, refresh the database portion of the Object Explorer pane to add the Query Store section.

Select Regressed Queries to open the Regressed Queries pane in SQL Server Management Studio. The Regressed Queries pane shows you the queries and plans in the query store. Use the drop down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).
Select a plan to see the graphical query plan. Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

To force a plan, select a query and plan, and then click Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Finding wait queries

Starting with SQL Server 2017 (14.x) CTP 2.0 and Azure SQL Database, wait statistics per query over time are available in Query Store.
In Query Store, wait types are combined into wait categories. The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management Studio v18 or higher. The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. Use the drop down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

Select a wait category by clicking on the bar and a detail view on the selected wait category displays. This new bar chart contains the queries that contributed to that wait category.

Use the drop down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). Select a plan to see the graphical query plan. Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Wait categories are combining different wait types into buckets similar by nature. Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

Previous experience

New experience

Action

High RESOURCE_SEMAPHORE waits per database

High Memory waits in Query Store for specific queries

Find the top memory consuming queries in Query Store. These queries are probably delaying further progress of the affected queries. Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.

High LCK_M_X waits per database

High Lock waits in Query Store for specific queries

Check the query texts for the affected queries and identify the target entities. Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.

High PAGEIOLATCH_SH waits per database

High Buffer IO waits in Query Store for specific queries

Find the queries with a high number of physical reads in Query Store. If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.

High SOS_SCHEDULER_YIELD waits per database

High CPU waits in Query Store for specific queries

Find the top CPU consuming queries in Query Store. Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. Focus on optimizing those queries - there could be a plan regression, or perhaps a missing index.

Configuration Options

The following options are available to configure query store parameters.

OPERATION_MODE
Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. The default value is 30. For SQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDS
Determines the frequency at which data written to the query store is persisted to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. The default value is 900 (15 min).

MAX_STORAGE_SIZE_MB
Configures the maximum size of the query store. If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. The default value is 100 MB. For SQL Database Premium edition, default is 1 GB and for SQL Database Basic edition, default is 10 MB.

INTERVAL_LENGTH_MINUTES
Determines the time interval at which runtime execution statistics data is aggregated into the query store. To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. This fixed time window is configured via INTERVAL_LENGTH_MINUTES. The default value is 60.

SIZE_BASED_CLEANUP_MODE
Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODE
Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). The default value on SQL Server (from SQL Server 2016 (13.x) to SQL Server 2017) is ALL, while on Azure SQL Database is AUTO.

MAX_PLANS_PER_QUERY
An integer representing the maximum number of plans maintained for each query. The default value is 200.

WAIT_STATS_CAPTURE_MODE
Controls if Query Store captures wait statistics information. Can be OFF or ON (default).

Query the sys.database_query_store_options view to determine the current options of the query store. For more information about the values, see sys.database_query_store_options.

For more information about setting options by using Transact-SQL statements, see Option Management.

Related Views, Functions, and Procedures

View and manage Query Storethrough Management Studio or by using the following views and procedures.

Key Usage Scenarios

Option Management

Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

Query Store status is determined by actual_state column. If it's different than the desired status, the readonly_reason column can give you more information.
When Query Store size exceeds the quota, the feature will switch to readon_only mode.

Get Query Store options

To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;

Setting Query Store interval

You can override interval for aggregating query runtime statistics (default is 60 minutes).

Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

Delete ad-hoc queries This deletes the queries that were only executed only once and that are more than 24 hours old.

Queries with multiple plans? These queries are especially interesting because they are candidates for regressions due to plan choice change. The following query identifies these queries along with all plans:

Queries that recently regressed in performance (comparing different point in time)? The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. Query compares all runtime stat intervals side by side.

Queries that recently regressed in performance (comparing recent vs. history execution)? The next query compares query execution based periods of execution. In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. It actually represents how much of additional duration recent executions introduced compared to history:

Maintaining Query Performance Stability

For queries executed multiple times you may notice that SQL Server uses different plans, resulting in different resource utilization and duration. With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. You can then force that optimal plan for future query execution.

You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

Force a plan for a query (apply forcing policy)

When a plan is forced for a certain query, SQL Server tries to force the plan in the optimizer. If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

Remove plan forcing for a query

To rely again on the SQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.