How do you use the Query Store to analyze query performance ?

Question

How do you use the Query Store to analyze query performance ?

Brief Answer

SQL Server’s Query Store is an indispensable feature that acts as a “flight data recorder” for your database. It automatically captures and persists a historical record of query execution plans, runtime statistics (CPU, I/O, memory, duration), and wait statistics. This continuous collection enables comprehensive performance analysis and proactive optimization.

My primary uses for Query Store include:

  • Identifying Performance Regressions: I use its historical data to pinpoint exactly when a query’s performance degraded, often correlating it with recent code deployments, index changes, or statistics updates. By comparing execution plans before and after the regression, I can quickly identify the root cause.
  • Diagnosing Bottlenecks with Wait Statistics: Query Store’s wait statistics are crucial for understanding *why* a query is slow. High waits like PAGEIOLATCH_SH (I/O contention) or CXPACKET (parallelism issues) guide me directly to the underlying problem, whether it’s inefficient indexing, outdated statistics, or resource contention.
  • Forcing Optimal Execution Plans: If a performance regression is due to the optimizer choosing a suboptimal plan, Query Store allows me to force a previously known good plan. This provides an immediate performance fix while I work on a more permanent solution like query rewriting or index tuning.
  • Monitoring Resource Consumption: It helps identify the most resource-intensive queries (CPU, memory, I/O), allowing me to prioritize optimization efforts on queries that have the greatest impact on overall system performance.

I configure Query Store (e.g., data retention, max storage size) to suit the database’s monitoring needs. I use T-SQL queries (sys.query_store_query, sys.query_store_runtime_stats) and SQL Server Management Studio’s Query Store reports to analyze data, view plans, and manage plan forcing. It serves as an excellent starting point for investigations, often guiding me to use other tools like DMVs or Extended Events for deeper real-time analysis.

Super Brief Answer

Query Store automatically collects a history of query execution plans, runtime statistics, and wait statistics. I use it to identify performance regressions, diagnose bottlenecks via wait stats, and monitor resource consumption. Critically, it allows me to force previously optimal execution plans to quickly resolve performance issues. It’s an indispensable tool for understanding and proactively optimizing SQL Server query performance.

Detailed Answer

SQL Server’s Query Store is an indispensable tool for database administrators and developers seeking to understand, monitor, and optimize query performance. It acts as a comprehensive historical repository of query execution data, enabling proactive and reactive performance tuning.

Direct Summary: Leveraging Query Store for Performance

Query Store automatically collects query history, including execution plans, resource consumption, and wait statistics. This enables you to analyze performance trends, identify problematic queries, pinpoint performance regressions, and compare different execution plan choices over time. It functions as a ‘flight data recorder’ for your database, providing invaluable historical data for targeted optimization.

What is SQL Server Query Store?

Query Store is a feature in SQL Server that automatically captures a history of queries, execution plans, and runtime statistics. It provides insights into workload performance by enabling you to quickly find performance differences caused by plan changes, resource contention, or other factors. It’s enabled at the database level and works continuously in the background.

Related To: Query Tuning, Query Store, Performance Monitoring, Diagnostics, Execution Plans, SQL Server Optimization

Key Capabilities for Performance Analysis

Automatic Query History Collection

Query Store persists query plans, runtime statistics, and wait events automatically. Once enabled at the database level, Query Store works behind the scenes, continuously capturing query information without manual intervention. This automatic collection makes it easy to track performance over time and investigate issues without having to set up tracing or profiling beforehand. It provides a complete history that is vital for understanding intermittent or historical performance problems.

Identifying Performance Regressions

One of the most powerful features of Query Store is its ability to identify performance regressions. It allows you to compare execution plans and metrics at different points in time. You can pinpoint precisely when a query started performing worse, identify changes in the execution plan that might be the cause, and analyze the impact on resource consumption. This historical context is crucial for diagnosing issues that appear after code deployments, index changes, or SQL Server upgrades.

In-depth Wait Statistics Analysis

Query Store collects wait statistics, which provide deep insights into why a query might be slow. By examining wait types like CXPACKET (indicating parallelism issues), PAGEIOLATCH_SH (buffer pool contention from I/O operations), or LCK_M_S (lock waits), you can identify the specific bottlenecks affecting your queries. This helps you move beyond just knowing a query is slow to understanding the root cause of the slowness.

Forcing Optimal Execution Plans

If analysis reveals that a performance regression is caused by a suboptimal execution plan (e.g., the optimizer choosing a less efficient plan after a statistics update), Query Store allows you to force a previously known good plan. This provides a quick and effective way to restore performance while you work on a more permanent solution, such as adding missing indexes, rewriting the query, or updating statistics.

Monitoring Resource Consumption

Query Store tracks the resource consumption (CPU, memory, I/O) of each query. This data helps you identify resource-intensive queries that might be impacting overall database performance. By focusing optimization efforts on these high-impact queries, you can achieve the biggest performance gains and improve the efficiency of your SQL Server instance.

Practical Applications & Real-World Scenarios

The following scenarios illustrate how Query Store is used in real-world performance tuning efforts:

Scenario 1: Pinpointing Performance Regressions

“In a recent project, we were experiencing intermittent performance slowdowns after deploying new code. Using Query Store, I visualized the average duration of our key queries over time. I noticed a clear spike in duration immediately following a specific deployment. By comparing the execution plans before and after the deployment, I discovered that a new index, intended to improve performance, was actually causing the optimizer to choose a less efficient plan. We reverted the index change, and the performance returned to normal. Query Store’s historical data and visualization capabilities made it very easy to pinpoint the exact time the regression occurred and correlate it with the deployment event.”

Scenario 2: Diagnosing Bottlenecks with Wait Stats

“We had a reporting query that was suddenly taking an excessively long time to complete. Looking at Query Store’s wait statistics for the query, I saw a high number of PAGEIOLATCH_SH waits. This indicated I/O contention, specifically around reading data pages from disk. Further investigation revealed that the statistics on a key table were outdated, leading to inefficient query plans that were scanning far more data than necessary. Updating the statistics resolved the I/O bottleneck and dramatically improved query performance.”

Scenario 3: Resolving Performance Issues with Indexing

“We had an e-commerce application where the checkout process was becoming slow during peak hours. Analyzing Query Store, I noticed a specific query related to inventory checks had a steadily increasing duration. The wait stats showed high CXPACKET waits, suggesting parallelism issues. Examining the execution plan, I found that the query was performing a table scan on a large inventory table. I added a missing index on the product ID and warehouse ID, which the query was filtering on. This allowed the query to use an index seek, reducing the I/O and resolving the parallelism bottleneck. The checkout process performance improved significantly.”

Scenario 4: Understanding Query Store Configuration

“When configuring Query Store, I consider the data retention period based on how long we typically need to analyze performance trends. We set it to 90 days, balancing historical analysis needs with storage space. For capture mode, we use ‘Auto’ in most cases, letting Query Store automatically manage the level of detail captured. However, for specific troubleshooting scenarios, we might temporarily switch to ‘All’ to capture all queries, including ad-hoc ones, even though it increases storage usage.”

Scenario 5: Integrating with Other Performance Tools

“Query Store gives me a great overview of query performance trends and potential regressions. When I identify a problematic query in Query Store, I often use DMVs like sys.dm_exec_requests and sys.dm_os_wait_stats to get a real-time view of its current execution and wait activity. If I need even deeper analysis, I use Extended Events to capture specific events related to the query’s execution, like lock acquisitions and releases. Query Store acts as a starting point for my investigations, guiding me to the right queries and timeframes to focus on with these other tools.”

Enabling and Using Query Store (T-SQL Examples)

Here are some essential T-SQL commands for enabling and interacting with Query Store:

-- Enable Query Store for the current database. Note that the Query Store is already on by default on newer versions of SQL Server (SQL Server 2016+).
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

-- Configure data retention and storage options (optional, but recommended for production).
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,  -- Ensure it's in read-write mode for capturing data
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), -- Remove queries older than 30 days
    MAX_STORAGE_SIZE_MB = 1024, -- Limit storage to 1GB
    SIZE_BASED_CLEANUP_MODE = AUTO  -- Automatically clean up when storage limit is reached
);

-- Find queries with high average duration (a common starting point for performance analysis).
SELECT q.query_id, qt.query_sql_text, rs.avg_duration, rs.count_executions, rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
ORDER BY rs.avg_duration DESC;

-- View execution plans for a specific query (replace [YourQueryId] with an actual query_id).
SELECT p.plan_id, p.query_plan
FROM sys.query_store_plan p
WHERE p.query_id = [YourQueryId];

-- Force a specific plan (use with extreme caution, and only after thorough testing).
-- Replace [YourQueryId] and [YourPlanId] with the actual IDs.
EXEC sp_query_store_force_plan @query_id = [YourQueryId], @plan_id = [YourPlanId];

-- Unforce a plan (to allow the optimizer to choose again).
-- Replace [YourQueryId] and [YourPlanId] with the actual IDs.
EXEC sp_query_store_unforce_plan @query_id = [YourQueryId], @plan_id = [YourPlanId];

-- Clean Query Store data for a specific query (e.g., after optimization).
-- Replace [YourQueryId] with the actual ID.
EXEC sp_query_store_remove_query @query_id = [YourQueryId];

-- Clear all data from Query Store for the current database.
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

Conclusion

Query Store is an essential feature for any SQL Server professional involved in performance tuning. Its ability to automatically collect, store, and present historical query data—including execution plans, runtime statistics, and wait events—provides unparalleled insight into database workload behavior. By leveraging Query Store, you can efficiently identify performance bottlenecks, diagnose regressions, and implement targeted optimizations, leading to a more stable and high-performing database environment.