Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Monitor performance by using the Query Store
In this articleApplies 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.
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
Use the Query Store page in SQL Server Management Studio
Use Transact-SQL statementsUse the
In Azure Synapse Analytics, enable the Query Store without additional options, for example:
For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL). Note Query Store cannot be enabled for the Information in the Query StoreExecution 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.
Common scenarios for using the Query Store feature are:
The Query Store contains three stores:
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.
Query Store for secondary replicasApplies 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. For complete information on Query Store for secondary replicas, see Query Store for Always On availability group secondary replicas. Use the Regressed Queries featureAfter 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 queriesStarting 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 selecting 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:
Configuration optionsFor the available options to configure Query Store parameters, see ALTER DATABASE SET options (Transact-SQL). Query the 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. View and manage Query Store through Management Studio or by using the following views and procedures. Query Store functionsFunctions help operations with the Query Store. Query Store catalog viewsCatalog views present information about the Query Store. Query Store stored proceduresStored procedures configure the Query Store. sp_query_store_consistency_check (Transact-SQL)1 1 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 Query Store maintenanceBest practices and recommendations for maintenance and management of the Query Store have been expanded in this article: Best practices for managing the Query Store. Performance auditing and troubleshootingFor more information about diving into performance tuning with Query Store, see Tune performance with the Query Store. Other performance topics:
See also
Next steps
FeedbackSubmit and view feedback for What are collected from several entities at the same point in time?Cross-sectional data are collected from several entities at the same, or approximately the same, point in time.
What is a quantity of interest that can take on different values?A variable is a characteristic that can be measured and that can assume different values.
Is a graphical summary of data previously summarized in a frequency distribution?Forrest Gump. A common graphical representation of quantitative data is a histogram. This graphical summary can be prepared for data previously summarized in either a frequency, relative frequency, or percent frequency distribution.
What shows the number of data items with values less than or equal to the upper class limit of each class?(A) Cumulative frequency distribution: A tabular summary of quantitative data showing the number of data values that are less than or equal to the upper class limit of each class.
|