Query Store Analysis

Lesson: Mastering Query Store Analysis

Introduction: The "Black Box" of Database Performance

In the world of database administration and software development, one of the most frustrating experiences is the "performance regression." You have a system that is running perfectly one day, only to have a critical report crawl to a halt the next. Historically, identifying why a query suddenly slowed down felt like detective work involving complex trace files, extended events, and a heavy dose of guesswork. You were often looking at a snapshot in time, hoping to catch the query in the act of being slow.

Query Store changes this dynamic entirely. Think of it as a flight data recorder for your database. It automatically captures a history of queries, execution plans, and runtime statistics, persisting this information even if the database is restarted or the server is rebooted. By providing a permanent record of how your queries performed over time, Query Store transforms performance tuning from a reactive, guesswork-driven task into a proactive, data-backed discipline. Understanding how to use Query Store is arguably the most important skill for anyone responsible for maintaining the health of a SQL-based application.

Understanding the Architecture of Query Store

To effectively use Query Store, you must first understand that it operates as an internal component of the database engine. It consists of two primary stores: the Query Store and the Plan Store. The Query Store tracks the query text and the objects involved, while the Plan Store tracks the specific execution plans associated with those queries. A single query might have multiple execution plans over time—perhaps because of changes in data distribution, index modifications, or statistics updates.

When a query executes, the database engine checks if the query is already in the Query Store. If it is, the engine updates the runtime statistics for that specific plan. If it is not, the engine creates a new entry. This process happens with minimal overhead, making it safe to enable in production environments. By capturing the "plan ID" alongside the "query ID," Query Store allows you to see exactly which version of an execution plan was used during a specific time interval, which is the key to identifying regressions.

Callout: Query Store vs. Traditional Tracing Traditional tracing tools like SQL Server Profiler or Extended Events are often "point-in-time" observers. They require you to be actively recording while the problem is happening. Query Store is "always on." It records data continuously in the background, meaning you can investigate a performance issue that happened at 3:00 AM yesterday without having needed to set up a trace in advance.

Enabling and Configuring Query Store

Before you can analyze performance, you must ensure Query Store is properly configured. While it is enabled by default in many modern versions of SQL Server and Azure SQL Database, you should always verify the settings to ensure they align with your workload requirements.

Step-by-Step: Enabling Query Store

1. Open your management tool (such as SSMS or Azure Data Studio) and connect to your database instance.
2. Right-click on your database and select Properties.
3. Navigate to the Query Store page.
4. Set the Operation Mode (Requested) to "Read Write."
5. Configure the Statistics Collection Interval. A 60-minute interval is a common default, but for high-transaction systems, you might want to consider 15 or 30 minutes to gain more granular data.

Important Configuration Parameters

• Max Size (MB): This defines how much disk space the Query Store can consume. If it hits this limit, it will stop capturing new data. You should monitor this regularly and set it large enough to hold at least a few weeks of data.
• Query Store Capture Mode: You can set this to "All," "Auto," or "None." "Auto" is usually the best choice, as it filters out insignificant queries (queries that run very quickly or have very little impact) to keep the store size manageable.
• Stale Query Threshold: This determines how long data is kept before it is purged. A setting of 30 to 60 days is usually sufficient for historical analysis.

Warning: Storage Management Always keep an eye on your disk space. If the Query Store reaches its maximum size, it will effectively switch to "Read-Only" mode. You will lose the ability to capture new performance data until you manually clear space or increase the size limit. Set up alerts to notify you when the Query Store reaches 80% capacity.

Analyzing Performance Regressions

The primary use case for Query Store is identifying "regressions." A regression occurs when a query that used to run in 50 milliseconds suddenly starts taking 5 seconds. Query Store makes this obvious by allowing you to compare current execution times against historical averages.

Using the Built-in Reports

Most graphical management tools include a "Regressed Queries" report. This report automatically identifies queries where the execution time has increased significantly over the last few time intervals. When you open this report, you will see a scatter plot or a list of queries. Clicking on a query will show you the plan history. If you see two different plan IDs for the same query ID, you have found the culprit.

Manual Analysis via T-SQL

While the graphical reports are helpful, querying the system views directly gives you more flexibility. The following query helps you identify queries that have multiple plans with varying performance:

SELECT 
    q.query_id, 
    qt.query_sql_text, 
    p.plan_id, 
    rs.avg_duration, 
    rs.count_executions
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt 
    ON q.query_text_id = qt.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.avg_duration > 1000 -- Look for queries taking > 1 second
ORDER BY rs.avg_duration DESC;

In this code, we join the four main Query Store tables. sys.query_store_query provides the base ID, sys.query_store_query_text gives us the actual text (so we know what the query looks like), sys.query_store_plan shows us the execution plan, and sys.query_store_runtime_stats provides the actual performance metrics like duration and CPU usage.

Forced Plan Execution: The "Emergency Brake"

One of the most powerful features of Query Store is the ability to "Force a Plan." Suppose you have a query that suddenly started using a slow, inefficient execution plan because the database engine's optimizer made a poor choice due to stale statistics or parameter sniffing. If you know that a previous execution plan was much faster, you can tell the database to ignore the new plan and force the use of the older, faster one.

How to Force a Plan

1. In the Query Store reports, identify the Query ID and the Plan ID that you want to force.
2. In your query window, execute the following command: EXEC sp_query_store_force_plan @query_id = [YourQueryID], @plan_id = [YourPlanID];
3. The database engine will now prioritize this plan for all future executions of that query.

Tip: Use Force Plan Sparingly Forcing a plan is a temporary fix, not a permanent solution. It masks the underlying problem (like missing indexes or outdated statistics). Always investigate why the optimizer chose the poor plan before deciding to force a different one. When you fix the underlying issue, be sure to unforce the plan to allow the optimizer to do its job again.

Best Practices for Query Store Maintenance

To keep Query Store running efficiently, you must treat it like any other database object. It requires maintenance, monitoring, and a clear strategy.

1. Monitor Size and Growth

As mentioned earlier, storage is the biggest risk. Use a monitoring script to check the sys.database_query_store_options view. If the actual_state_desc is "READ_ONLY," your Query Store has hit its limit and is no longer capturing data.

2. Clean Up Old Data

If your Query Store is getting too large, you don't necessarily need to wipe it entirely. You can use the sp_query_store_remove_query or sp_query_store_remove_plan procedures to prune specific, outdated entries. This is particularly useful if you have a massive deployment script that generated thousands of unique query IDs that are no longer relevant.

3. Use Query Hints

If you find that a query frequently needs a specific hint (like OPTION (RECOMPILE) or a join hint) to perform well, you can use Query Store to apply these hints without changing the application code. This is done through "Query Store Hints," which allow you to inject hints into the execution pipeline at the server level.

4. Regularly Review the "Top Resource Consuming Queries"

Don't wait for a performance crisis to look at Query Store. Set aside time weekly to look at the "Top Resource Consuming Queries" report. Look for queries that are at the top of the list for CPU or logical reads. Often, you can optimize these queries—perhaps by adding a missing index—before they ever become a user-reported problem.

Common Pitfalls and How to Avoid Them

Even with a tool as powerful as Query Store, it is easy to fall into common traps. Let's look at the most frequent mistakes administrators make.

Mistake 1: Ignoring Parameter Sniffing

Parameter sniffing occurs when a query is optimized based on the values provided during the first execution. If the next execution uses different values, the plan might be suboptimal. Developers often see this in Query Store as a query that is fast for some parameters and slow for others.

• The Fix: Don't just force a plan. Look at why the plan is sensitive to parameters. Consider using OPTIMIZE FOR UNKNOWN or creating plan guides if the issue persists, but always verify the impact in Query Store before and after the change.

Mistake 2: Over-reliance on "Forced Plans"

Some administrators treat "Forced Plan" as a magic button to fix all performance issues. The danger here is that data changes over time. A plan that was fast today might be inefficient in six months because the data distribution in your tables has changed significantly. If you leave a plan forced, you prevent the optimizer from adapting to these changes.

• The Fix: If you force a plan, add a reminder to your calendar to re-evaluate it in 30 or 60 days.

Mistake 3: Misinterpreting "Average" Metrics

Query Store reports often show "Average Duration." Be careful with averages. A query that runs in 1 millisecond 1,000 times and 10 seconds once will have an average duration that looks perfectly fine.

• The Fix: Look at the distribution of execution times. If your tools support it, look at the "Maximum" or "Standard Deviation" metrics to see if there is a wide variance in performance.

Callout: The Importance of Context Query Store does not exist in a vacuum. It shows you the what and the when, but you still need to understand the why. A slow query in Query Store might be slow because of a blocking transaction from another session, or it might be slow because of a hardware bottleneck. Always cross-reference Query Store data with DMV (Dynamic Management View) data regarding locks and wait statistics.

Advanced Techniques: Integrating Query Store with Automation

For large environments with hundreds of databases, you cannot manually check Query Store for every single database. You should move toward an automated approach.

Automated Monitoring Script

You can create a custom monitoring script that runs as a SQL Agent job. This script can check for any "Regressed Queries" across all databases and email a report to the DBA team.

-- Example: Identifying regressed queries across all databases
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'USE ' + QUOTENAME(name) + '; 
    SELECT ''' + name + ''' AS DBName, q.query_id, rs.avg_duration 
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    JOIN sys.query_store_query q ON p.query_id = q.query_id
    WHERE rs.avg_duration > 5000; '
FROM sys.databases WHERE state = 0;

EXEC sp_executesql @sql;

This script iterates through every online database on the instance, pulls the query IDs where the average duration is over 5 seconds, and returns them in a single result set. You can pipe this output into a notification system to stay ahead of performance issues.

Comparison: Query Store vs. Extended Events

It is common for students to ask when to use Query Store and when to use Extended Events. They serve different purposes, though they overlap in some areas.

Use Query Store for "What is my slowest query?" and use Extended Events for "Why did this specific transaction deadlock at 2:00 PM?"

Troubleshooting Query Store Data Gaps

Occasionally, you might notice gaps in your Query Store data. This is usually due to one of three things:

1. The database was offline: If the database was taken offline or crashed, data for that interval might not have been flushed to disk.
2. The "Wait Stats" capture is disabled: Ensure that WAIT_STATS_CAPTURE_MODE is set to 'ON' if you want to see what your queries are waiting on (like I/O or locks).
3. The service tier is too low: In cloud environments, very low service tiers might have limitations on the amount of telemetry data they can process.

If you see gaps, verify your configuration and check the SQL Server error logs for any messages related to Query Store failures.

Quick Reference: The Lifecycle of a Performance Investigation

When a user reports a performance issue, follow this structured process:

1. Verify the scope: Is the entire system slow, or just one specific report?
2. Check Query Store: Open the Regressed Queries report. Is there a spike in duration?
3. Analyze the Plan: Compare the current plan with the historical "good" plan. Look for differences in operators (e.g., a Seek turning into a Scan).
4. Examine Wait Statistics: Look at the wait types associated with the slow query. Is it waiting on PAGEIOLATCH_SH (disk speed)? Is it waiting on LCK_M_X (blocking)?
5. Mitigate: If it's a bad plan, force the good one. If it's a missing index, add the index.
6. Validate: After the fix, monitor the Query Store for the next few hours to ensure the performance returns to the expected baseline.
7. Clean up: Remove the forced plan if the underlying issue is resolved.

Real-World Scenario: The Unexpected Index Scan

Imagine a large e-commerce database. A query that fetches order details for a customer has been running in 10ms for months. Suddenly, it jumps to 500ms.

You open Query Store and find that a new execution plan was introduced. Looking at the plan, you see that the optimizer switched from an "Index Seek" on the CustomerID column to a "Clustered Index Scan" on the entire Orders table. Why?

You check the statistics on the Orders table. It turns out that a massive cleanup job deleted 80% of the data, and the statistics were not updated. The optimizer thought the table was still huge and decided that a scan was faster than a seek.

The Fix: You update the statistics using UPDATE STATISTICS Orders. The next time the query runs, the optimizer re-evaluates, sees the new distribution, and switches back to the efficient "Index Seek." You then check Query Store to confirm that the performance is back to 10ms. This is the perfect example of using Query Store to diagnose a problem that isn't just about the query code itself, but about the health of the database environment.

Key Takeaways

• Query Store is your history book: It provides a permanent, searchable record of execution plans and performance metrics, allowing for accurate "before and after" comparisons.
• Proactive beats reactive: Use the built-in reports to identify performance trends before they become critical user-facing outages.
• Understand the Plan: Performance problems are almost always about the execution plan. Use Query Store to identify exactly when and why a plan changed.
• Force with caution: Forcing a plan is a valid emergency tool, but it is not a substitute for proper indexing and statistics maintenance. Always treat forced plans as temporary.
• Monitor the store itself: Don't let your recording tool become a performance bottleneck or a storage liability. Keep an eye on the size and configuration settings.
• Context is king: Query Store tells you that a query is slow, but you must use other tools (like DMV wait stats) to understand if the slowness is due to blocking, resource contention, or poor query design.
• Automate your oversight: For production systems, don't rely on manual checks. Use scripts to alert your team to regressed queries as soon as they appear in the data.

By mastering these concepts, you shift from being a database administrator who "hopes" the system stays fast, to one who "knows" exactly how the system is performing and has the tools to fix it when it drifts. This level of visibility is the hallmark of a professional-grade database management strategy.