Using SQL Server Query Store to Catch and Fix Query Plan Regressions

A reporting query that used to finish in two seconds suddenly takes four minutes. Nothing was deployed, no index was dropped, and the query text is identical. The likely culprit is a plan regression: SQL Server's query optimizer—the component that chooses how to execute a query—picked a different execution plan, and the new one is dramatically slower for your current data distribution.

Instead of guessing, you can use Query Store, a per-database feature that acts like a flight recorder for query performance. It persists execution plans and their runtime statistics over time, so you can compare what the optimizer did yesterday against what it is doing today. When you find the regression, you can pin the query to a known-good plan as an immediate tactical fix.

What Query Store Actually Captures

Query Store does not record every query forever. It maintains a repository of compile-time metadata (the plan shape and query text) and runtime statistics (duration, CPU time, logical reads, and waits) for each executed query. Because the data lives inside the user database, you can query it with normal T-SQL rather than relying on a third-party monitoring tool.

The core views are:

• sys.query_store_plan — one row per captured execution plan, including the plan XML and whether it is currently forced.
• sys.query_store_runtime_stats — aggregated runtime metrics per plan over intervals.
• sys.query_store_wait_stats — wait categories observed during execution, useful for distinguishing CPU-bound plans from I/O-bound ones.

This historical record is what makes regression diagnosis possible. You are no longer limited to whatever plan happens to be in the cache right now.

Enabling Query Store and Balancing Overhead

Query Store has been available since SQL Server 2016 and is also present in Azure SQL Database, though some defaults and DMV columns vary by version. Confirm the exact view schemas against your specific version. It is enabled per database. Run the following in the context of the target database (not master):

ALTER DATABASE CURRENT SET QUERY_STORE = ON;

This requires ALTER permission on the database. Once enabled, check the active settings:

SELECT operation_mode,
       data_flush_interval_seconds,
       size_based_cleanup_mode,
       max_storage_size_mb,
       current_storage_size_mb
FROM sys.database_query_store_options;

Three settings matter for day-to-day operations:

1. Capture mode. The default aggressive setting captures all queries. On databases with very high ad-hoc query volumes—short, one-off statements that are unlikely to repeat—this can increase Query Store file I/O and consume disk quickly. Consider a custom or auto mode if overhead becomes noticeable.
2. Data flush interval. Controls how often in-memory statistics are written to disk. Longer intervals reduce write overhead but increase the window of data loss if the instance restarts.
3. Size-based cleanup. When enabled, Query Store automatically purges older data when it reaches the storage limit. If cleanup cannot keep pace, the store can transition to read-only and stop capturing new plans.

The trade-off is straightforward: richer capture gives you better diagnostics, but it costs storage and background I/O. Size the store according to your workload, not the default.

Worked Example: Identifying and Forcing a Better Plan

Suppose a single query in your application has regressed. You can compare its recent plans by joining the plan and runtime stats views. The following query, run in the user database, summarizes each plan's average duration and CPU time:

SELECT p.plan_id,
       p.is_forced_plan,
       rs.count_executions,
       rs.avg_duration,
       rs.avg_cpu_time
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs
  ON p.plan_id = rs.plan_id
WHERE p.query_id = (
    SELECT TOP (1) q.query_id
    FROM sys.query_store_query AS q
    WHERE q.object_id = OBJECT_ID(N'dbo.YourReportProcedure')
)
ORDER BY rs.last_execution_time DESC;

Replace dbo.YourReportProcedure with the actual object name. Look for a plan with low avg_duration and avg_cpu_time from an earlier period, and a newer plan where those values spiked. If the older plan is still valid for the current schema, you can force it:

EXEC sp_query_store_force_plan
    @query_id = <query_id>,
    @plan_id = <plan_id>;

Run this in the user database with appropriate permissions, typically database owner or an elevated DBA role. After forcing, confirm that the plan is marked as forced in sys.query_store_plan.is_forced_plan. Then execute the query again and check whether the runtime stats show the expected duration returning to normal.

Why Forcing Is a Tactical Fix, Not a Permanent Guarantee

Plan forcing pins the optimizer to a specific plan, but it does not override reality. If the underlying schema changes—an index is dropped, a column is altered, or a significant shift in data distribution makes the plan invalid—the optimizer can ignore the forced plan or fall back to a new one. Forcing buys you time; it does not replace fixing the root cause, whether that means updating statistics, adding a covering index (one that includes all columns the query needs), or rewriting the query.

There is also operational risk. Query Store writes data asynchronously, so a busy system can see increased file I/O. If size_based_cleanup_mode is off and storage fills, the store can become read-only and stop capturing new plans, leaving you blind to the next regression.

Practical Verification

After enabling Query Store or forcing a plan, do not assume it worked. Check sys.database_query_store_options for current_storage_size_mb and read_write status to ensure the store is healthy. For a forced plan, verify that is_forced_plan is set and that recent rows in sys.query_store_runtime_stats reflect the plan you intended. If the optimizer has already fallen back to a different plan, the forced flag may still be set, but the runtime evidence will tell you it is not being used effectively.

Query Store turns plan regressions from mysteries into comparisons. Use it to identify the better plan quickly, force it to restore service, and then treat the forced plan as a signal that the database design or query needs a more durable fix.