Monitor performance by using the Query Store

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (dedicated SQL pool only)

The Query Store feature provides you with insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Query Store 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.

• For information about operating the Query Store in Azure SQL Database, see Operating the Query Store in Azure SQL Database.
• For information on discovering actionable information and tune performance with the Query Store, see Tune performance with the Query Store.
• For information on shaping query plans without changing application code, see Query Store hints.

Important

If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

Enable the Query Store

• Query Store is enabled by default for new Azure SQL Database and Azure SQL Managed Instance databases.
• Query Store is not enabled by default for SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x), or SQL Server 2022 (16.x) Preview. To enable features to better track performance history, troubleshoot query plan related issues, and enable new capabilities in SQL Server 2022 (16.x) Preview, we recommend enabling Query Store on new and existing databases.
• Query Store is not enabled by default for new Azure Synapse Analytics databases.

Use the Query Store page in SQL Server Management Studio

1. In Object Explorer, right-click a database, and then select Properties.

   Note

   Requires at least version 16 of Management Studio.

2. In the Database Properties dialog box, select the Query Store page.

3. In the Operation Mode (Requested) box, select Read Write.

Use Transact-SQL statements

Use the ALTER DATABASE statement to enable the query store for a given database. For example:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

In Azure Synapse Analytics, enable the Query Store without additional options, for example:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL).

Note

Query Store cannot be enabled for the master or tempdb databases.

Important

For information on enabling Query Store and keeping it adjusted to your workload, refer to Best Practice with the Query Store.

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.

Note

Query Store collects plans for DML Statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT.

Query Store does not collect data for natively compiled stored procedures by default. Use sys.sp_xtp_control_query_exec_stats to enable data collection for natively compiled stored procedures.

Wait stats are another source of information that helps to troubleshoot performance in the Database Engine. For a long time, wait statistics were available only on instance level, which made it hard to backtrack waits to a specific query. Starting with SQL Server 2017 (14.x) and Azure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

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.
• Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
• Audit the history of query plans for a given query.
• 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.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

Query Store for secondary replicas

APPLIES TO: SQL Server (Starting with SQL Server 2022 (16.x) Preview)

The Query Store for secondary replicas feature enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. When Query Store for secondary replicas is enabled, replicas send the query execution information that would normally be stored in the Query Store back to the primary replica. The primary replica then persists the data to disk within its own Query Store. In essence, there is one Query Store shared between the primary and all secondary replicas. The Query Store exists on the primary replica and stores data for all replicas together.

Note

Replica set or replica group: A replica set is defined as being all unnamed replicas that share a role (primary, secondary, geo secondary, geo primary), or as being an individual named replica.

The data stored about queries can be analyzed as workloads on a replica set basis. Query Store for replicas provides the ability to monitor and adjust the performance of any unique, read-only workloads that might be executing against secondary replicas.

Enable Query Store for secondary replicas

Before using Query Store for secondary replicas, you need to have an Always On availability group set up and configured.

Important

APPLIES TO: SQL Server 2022 (16.x) CTP 2.x

You must enable the following set of trace flags before you can enable Query Store for secondary replicas: 12606, 12607, 12608, 12610, T12624. To enable these trace flags:

1. Open the services management console (services.msc from the Run menu).
2. Right-click on the SQL Server service for SQL Server 2022 CTP 2 and select Properties.
3. If the service status is Running, select Stop. This will stop the installed instance.
4. In the Start parameters box, add the values: -T12606 -T12607 -T12608 -T12610 -T12624
5. Select Start to start the service.
6. Select OK.

Enable Query Store for secondary replicas using ALTER DATABASE SET options (Transact-SQL). The following example enables Query Store on the primary database, and then on secondary replicas. To execute this code, connect to the database on the primary replica.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = ON ( 
        OPERATION_MODE = READ_WRITE 
);
GO

You can validate that Query Store is enabled on a secondary replica by connecting to the database on the secondary replica and executing the following Transact-SQL:

SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO

The following sample results from querying sys.database_query_store_options indicate that the Query Store is in a read/write state for the secondary. The readonly_reason of 8 indicates that the query was run against a secondary replica. These results indicate that Query Store has been enabled successfully on the secondary replica.

desired_state	desired_state_desc	actual_state	actual_state_desc	readonly_reason
2	READ_WRITE	2	READ_WRITE	8

Performance considerations for Query Store for secondary replicas

The channel used by secondary replicas to send query information back to the primary replica is the same channel used to keep secondary replicas up to date. Data is stored in the same tables on the primary replica that Query Store uses for queries executed on the primary replica, which causes the size of Query Store to grow.

Thus, when a system is under significant load, you may notice some slowdown because of the channel being overloaded. Further, the same adhoc query capture issues that exist for Query Store today will continue for workloads run on secondary replicas. Learn more about how to Keep the most relevant data in Query Store.

Disable Query Store for secondary replicas

To disable Query Store for secondary replicas, connect to the database on the primary replica and run the following code:

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = OFF;
GO

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.

Note

For Azure Synapse Analytics, Query Store views are available under System Views in the database portion of the Object Explorer pane.

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, then select Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Find waiting queries

Starting with SQL Server 2017 (14.x) 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 most 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

For the available options to configure Query Store parameters, see ALTER DATABASE SET options (Transact-SQL).

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 examples about setting configuration options using Transact-SQL statements, see Option Management.

Note

For Azure Synapse Analytics, the Query Store can be enabled as on other platforms but additional configuration options are not supported.

Related views, functions, and procedures

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

Query Store functions

Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Query Store catalog views

Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Query Store stored procedures

Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)¹

¹ In extreme scenarios Query Store can enter an ERROR state because of internal errors. Starting with SQL Server 2017 (14.x), if this happens, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure in the affected database. See sys.database_query_store_options for more details described in the actual_state_desc column description.

Query Store Maintenance

Option management

This section provides some guidelines on managing Query Store feature itself.

Query Store state

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 sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Query Store status is determined by the 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 read_only mode and provide a reason. For information on reasons, see sys.database_query_store_options (Transact-SQL).

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;

Set the Query Store interval

You can override interval for aggregating query runtime statistics (default is 60 minutes). New value for interval is exposed through sys.database_query_store_options view.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

Note

For Azure Synapse Analytics, customizing Query Store configuration options, as demonstrated in this section, is not supported.

Query Store space usage

To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Set Query Store options

You can set multiple Query Store options at once with a single ALTER DATABASE statement.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

For the full list of configuration options, see ALTER DATABASE SET Options (Transact-SQL).

Clean up the space

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.

In Azure Synapse Analytics, clearing the query store is not available. Data is automatically retained for the past 30 days.

Delete ad-hoc queries

This purges adhoc and internal queries from the Query Store so that the Query Store does not run out of space and remove queries we really need to track.

SET NOCOUNT ON
-- This purges adhoc and internal queries from 
-- the Query Store in the current database 
-- so that the Query Store does not run out of space 
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

You can define your own procedure with different logic for clearing up data you no longer want.

The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. You can also:

• Use sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
• Use sp_query_store_remove_plan to remove a single plan.

Performance auditing and troubleshooting

For more information about diving into performance tuning with Query Store, see Tune performance with the Query Store.

Other performance topics:

• Query Store Usage Scenarios

See also

• Query Store Stored Procedures (Transact-SQL)
• Query Store Catalog Views (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Live Query Statistics
• Activity Monitor
• How Query Store Collects Data
• Monitor and Tune for Performance
• Performance Monitoring and Tuning Tools
• Using the Query Store with In-Memory OLTP

Next steps

• Best Practice with the Query Store
• Open Activity Monitor (SQL Server Management Studio)
• Tune performance with the Query Store