Diagnosing Query Regressions with SQL Server Query Store

The problem: a query that suddenly got slower

You shipped a small schema change—maybe a new index or an updated statistics job. A stored procedure that ran in two seconds yesterday now takes thirty. You open the execution plan, and it looks fine in isolation. The real issue is that you have no history: the old plan is gone, and the optimizer has silently rewritten its strategy. Without a baseline, you are guessing.

SQL Server Query Store closes that gap. Think of it as a flight recorder for query performance. Once enabled, it captures query text, execution plans, runtime statistics, and wait information for every query executed in the database, storing the data in internal tables inside the same user database. This gives you a time machine: you can compare what a query did before and after a change, and if the optimizer picked a worse plan, you can force the old one back.

What Query Store actually captures

Query Store does not replace the plan cache. The plan cache is volatile and cleared by restarts, memory pressure, or recompiles. Query Store persists data to disk in internal tables, aggregated over time intervals you control. For each query, it records:

• The normalized query text, so cosmetic differences in literals do not create separate entries.
• Every execution plan the optimizer produces for that query.
• Runtime statistics such as duration, CPU time, logical reads, and writes.
• Wait statistics, which help you see whether a query is blocked on I/O, locks, or parallelism.

The data is aggregated into intervals—60 minutes by default—so you can trend a query over days or weeks without drowning in individual executions.

Turning it on without creating a new problem

Query Store is available in SQL Server 2016 and later, and in Azure SQL Database. Enabling it is a database-scoped operation. Run this on the target database, not in master:

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

You need ALTER permission on the database or membership in db_owner. The defaults are conservative: maximum storage is 100 MB, and the capture mode collects everything. On a busy OLTP system, 100 MB can fill in days. When the size limit is reached, Query Store either purges old data or stops collecting new data, depending on your cleanup settings—leaving you blind exactly when you need it.

Set the limits deliberately. A common starting point for a moderately busy database is 500 MB to 2 GB, with automatic size-based cleanup enabled:

ALTER DATABASE YourDatabase SET QUERY_STORE (
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 30,
    STALE_QUERY_THRESHOLD_DAYS = 30,
    QUERY_CAPTURE_MODE = AUTO
);

QUERY_CAPTURE_MODE = AUTO skips trivial queries and stored procedures that execute rarely, reducing overhead. Statistics collection is synchronous by default, which adds a small latency cost. If you run an ultra-low-latency workload, benchmark before and after enabling the feature in a non-production environment.

Finding the regression

After Query Store has collected data, you have two ways to investigate. In SQL Server Management Studio, expand Object Explorer → Databases → YourDatabase → Query Store → Regressed Queries. The report compares execution metrics across time intervals and flags queries where a newer plan is consistently slower.

If you prefer T-SQL, the core dynamic management views are sys.query_store_query, sys.query_store_plan, and sys.query_store_runtime_stats. The typical pattern is to join them and compare average duration for the same query_id across different plan_id values and time windows.

A worked example: catching a bad plan after a schema change

Suppose you added a covering index to support a new report, and the nightly procedure usp_GetCustomerOrders regressed. First, verify Query Store is active:

SELECT is_query_store_on, actual_state_desc
FROM sys.database_query_store_options;

You want is_query_store_on = 1 and actual_state_desc = READ_WRITE. If it says READ_ONLY, the storage limit has been reached and new data is not being captured.

Next, find the query and its recent plans. Run this on YourDatabase:

SELECT
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration,
    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_plan p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs
    ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%usp_GetCustomerOrders%'
ORDER BY rs.last_execution_time DESC;

Look for multiple plan_id rows for the same query_id. If the plan that appeared after your index change has a higher avg_duration than the plan from before, you have found the regression. Force the known-good plan as a temporary bridge while you investigate the root cause:

EXEC sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;

Replace 123 and 456 with the actual values from the query above. The forced plan remains in effect until you unforce it or the query is recompiled under conditions that invalidate the plan. To revert, run EXEC sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;. Treat forcing as a tourniquet, not a cure—the underlying schema or statistics issue still needs a permanent fix.

The limitation nobody tells you about

Query Store is not free, and it is not infinite. The default 100 MB ceiling is easy to hit. If SIZE_BASED_CLEANUP is off and the limit is reached, Query Store switches to READ_ONLY and silently stops capturing. Your regression data disappears. If cleanup is on, it purges the oldest data, which may include the exact baseline you need.

Monitor it. The sys.database_query_store_options view also exposes current storage usage. Add a check to your monitoring stack that alerts when actual_state_desc is not READ_WRITE.

What to do next

1. Enable Query Store on your critical databases with a storage limit sized to your workload, not the default.
2. Verify actual_state_desc = READ_WRITE after enabling.
3. After every deployment or index change, open the Regressed Queries report.
4. Use sp_query_store_force_plan to stabilize production while you fix the underlying cause.
5. Alert on storage saturation so the feature does not go dark when you need it most.

Query Store will not tell you why the optimizer changed its mind. It will, however, give you the evidence to stop guessing and start fixing.