Monitoring performance by using the Query Store

In this, we will learn about monitoring performance by using the Query Store.

The Query Store is a feature in Microsoft SQL Server that helps you monitor the performance of your database queries over time. It provides a historical record of query execution plans and runtime statistics, which can be used to troubleshoot performance issues and optimize query performance.

Steps to use the Query Store to monitor performance

Here are the steps to use the Query Store to monitor performance:

Enable the Query Store: Before you can use the Query Store to monitor performance, you need to enable it for your database. You can do this by right-clicking on your database in SQL Server Management Studio, selecting Properties, and then selecting the Query Store page. From there, you can enable the Query Store and configure its settings.
Collect Query Performance Data: Once the Query Store is enabled, it will start collecting data about query performance. By default, it collects data about query plans and runtime statistics every hour. However, you can configure the collection interval and other settings as needed.
Analyze Query Performance Data: You can use the Query Store to analyze query performance data in several ways. For example, you can use the Query Store reports to view top resource-consuming queries, identify query regressions, and compare query performance over time. You can also use the built-in views and functions to query the Query Store data directly and create your own custom reports and dashboards.
Troubleshoot Performance Issues: If you notice a performance issue with a specific query, you can use the Query Store to troubleshoot the issue. For example, you can compare the execution plans and runtime statistics of the query over time to identify changes that may be affecting performance. You can also use the Query Store to force a specific execution plan for a query or revert to a previous plan if needed.

Enabling the Query Store

Query Store is not enabled by default for new SQL Server and Azure Synapse Analytics databases. For new Azure SQL Database databases, however, it is enabled by default.

Use the Query Store Page in SQL Server Management Studio

Firstly, in Object Explorer, right-click a database, and then click Properties.
Secondly, in the Database Properties dialog box, select the Query Store page.
Lastly, in the Operation Mode (Requested) box, select Read Write.

Use Transact-SQL Statements

To activate the query store for a database, use the ALTER DATABASE command. Consider the following scenario:

SQL
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

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. However, the procedure cache (where cached query plans are stored) only stores the latest execution plan.
- Further, 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.
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. However, this 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.

SQL
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Further, Common scenarios for using the Query Store feature are:

Firstly, 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.
Secondly, determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
Thirdly, identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
Then, audit the history of query plans for a given query.
After that, analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
Next, identify top n queries that are waiting on resources.
Lastly, understand wait nature for a particular query or plan.

Further, there is following query that returns information about queries and plans in the Query Store.

SQL
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 ;

Finding waiting queries

Starting with SQL Server 2017 (14.x) and Azure SQL Database, wait statistics per query over time are available in Query Store. Wait types, on the other hand, are merged into wait categories in Query Store. sys.query store wait stats contains the mapping of wait categories to wait for types (Transact-SQL).

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

SQL Server 2017 Query Wait Statistics in SSMS Object Explorer — Image Source: Microsoft

Next, select a wait category by clicking on the bar and a detail view on the selected wait category displays. However, this new bar chart contains the queries that contributed to that wait category.
Lastly, 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). Then, select a plan to see the graphical query plan. However, buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Key Usage Scenarios

Option Management

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

Is Query Store currently active?

Because Query Store keeps its data in the user database, it has a size restriction (set by MAX_STORAGE_SIZE_MB). If the data in Query Store reaches that limit, the store will switch from read-write to read-only mode and stop gathering 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.

SQL
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 actual_state column. If it’s different than the desired status. Moreover, the readonly_reason column can give you more information. When Query Store size exceeds the quota, the feature will switch to read_only mode.

Get Query Store options

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

SQL
SELECT * FROM sys.database_query_store_options;

Setting Query Store interval

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

SQL
ALTER DATABASE
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Query Store space usage

Next, to check current the Query Store size and limit execute the following statement in the user database.

SQL
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.

SQL
ALTER DATABASE
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = );

Set Query Store options

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

SQL
ALTER DATABASE
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
);

Performance Auditing and Troubleshooting

Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

Firstly, Last n queries executed on the database?

SQL
SELECT TOP 10 qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

Secondly, Number of executions for each query?

SQL
SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;

After that, The number of queries with the longest average execution time within last hour?

SQL
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Then, The number of queries that had the biggest average physical I/O reads in last 24 hours, with corresponding average row count and execution count?

SQL
SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,
q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Maintaining query performance stability

You may see that SQL Server employs various plans for queries that are run several times, resulting in varied resource use and duration. Query Store, on the other hand, can recognise when query performance has deteriorated and decide the best strategy for a certain time period. You may even use that ideal strategy to compel future query execution.

Furthermore, given a query containing parameters, you may detect inconsistencies in query performance (either auto-parameterized or manually parameterized). You can find the plan that is quick and optimum for all or most of the parameter values among the many plans and force it. And, maintaining predictable performance across a broader range of user circumstances.

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. However, if plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

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

However, 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.

Plan forcing support for fast forward and static cursors

Starting with SQL Server 2019 (15.x) and Azure SQL Database (all deployment models), Query Store supports the ability to force query execution plans for fast forward and static Transact-SQL and API cursors. Further, forcing is supported via sp_query_store_force_plan or through SQL Server Management Studio Query Store reports.

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.

SQL
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Monitoring performance by using the Query Store DP-300 online course

DP-300 Exam Practice Questions

Question: You notice that a particular query is running slower than usual. What should you do first to investigate the issue?

A. Run a Profiler trace on the query

B. Check the query execution plan in the Query Store

C. Restart the SQL Server instance

D. Check the server’s hardware resources

Answer: b. Check the query execution plan in the Query Store

Explanation: The Query Store keeps a record of query execution plans over time, so checking the execution plan in the Query Store can help you identify any changes that may be affecting performance.

Question: You want to compare the performance of a specific query between two different time periods. Which feature of the Query Store would you use?

A. Query Store reports

B. Query Store views

C. Query Store functions

D. Query Store procedures

Answer: a. Query Store reports

Explanation: The Query Store reports provide a graphical representation of query performance over time, making it easy to compare performance between different time periods.

Question: You want to view the top 10 resource-consuming queries in your database. Which Query Store view would you use?

A. sys.query_store_query

B. sys.query_store_query_text

C. sys.query_store_runtime_stats

D. sys.query_store_plan

Answer: c. sys.query_store_runtime_stats

Explanation: The sys.query_store_runtime_stats view contains information about the resource usage of each query, allowing you to identify the top resource-consuming queries in your database.

Question: You want to force a specific execution plan for a particular query. Which feature of the Query Store would you use?