How to find and fix query plan regressions in SQL Server with Query Store

What a plan regression is

A query that ran in 40 ms yesterday takes 4 seconds today, and nobody changed the query. Most of the time the query got a new execution plan: SQL Server recompiled it and chose differently. Common triggers:

• A statistics update, automatic or manual, changed the row estimates.
• The plan was compiled for an unusual parameter value (parameter sensitivity) after a restart, a failover or a plan cache eviction.
• An index was added, dropped or rebuilt, or the database compatibility level changed.

The plan cache only holds the current plan, so it can't show you what changed. Query Store can: it keeps every plan a query has used, with runtime statistics per plan per time interval. That history is what lets you find the regression, prove it, and go back to the plan that worked.

1. Check Query Store is recording

Query Store is on by default for new databases from SQL Server 2022, and in Azure SQL. On SQL Server 2016 to 2019, and on databases upgraded or restored from those versions, check before you rely on it.

SELECT actual_state_desc,
       desired_state_desc,
       readonly_reason,
       query_capture_mode_desc,
       interval_length_minutes,
       stale_query_threshold_days,
       current_storage_size_mb,
       max_storage_size_mb
FROM sys.database_query_store_options;

• READ_WRITE: recording. Go on to step 2.
• READ_ONLY when you asked for READ_WRITE: it stopped recording. readonly_reason says why; most often the store reached max_storage_size_mb. Raise the limit, then set it back to READ_WRITE.
• OFF: turn it on. It only records from that point, so a regression that happened before has no history to compare.

ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

ALTER DATABASE CURRENT SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

2. Find the queries that regressed

Compare each query's average duration in a recent window with a longer history window. Query Store stores an average per plan per interval, so weight each average by its execution count before you combine intervals; a plain average of averages overstates rarely run plans.

This query compares the last 24 hours with the 7 days before that, keeps queries that used more than one plan and got at least twice as slow, and ranks them by the extra time they cost in the recent window.

DECLARE @recent_start  datetimeoffset = DATEADD(hour, -24, SYSDATETIMEOFFSET());
DECLARE @history_start datetimeoffset = DATEADD(day,  -8,  SYSDATETIMEOFFSET());

WITH per_window AS (
    SELECT p.query_id,
           w.is_recent,
           SUM(rs.count_executions)                  AS execs,
           SUM(rs.avg_duration * rs.count_executions) AS total_us
    FROM sys.query_store_runtime_stats AS rs
    JOIN sys.query_store_runtime_stats_interval AS i
      ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    CROSS APPLY (SELECT CASE WHEN i.start_time >= @recent_start THEN 1 ELSE 0 END AS is_recent) AS w
    WHERE i.start_time >= @history_start
    GROUP BY p.query_id, w.is_recent
),
cmp AS (
    SELECT query_id,
           SUM(CASE WHEN is_recent = 1 THEN execs END)    AS recent_execs,
           SUM(CASE WHEN is_recent = 1 THEN total_us END)
             / NULLIF(SUM(CASE WHEN is_recent = 1 THEN execs END), 0) AS recent_avg_us,
           SUM(CASE WHEN is_recent = 0 THEN total_us END)
             / NULLIF(SUM(CASE WHEN is_recent = 0 THEN execs END), 0) AS history_avg_us
    FROM per_window
    GROUP BY query_id
)
SELECT TOP (20)
       c.query_id,
       (SELECT COUNT(*) FROM sys.query_store_plan AS p2 WHERE p2.query_id = c.query_id) AS plans,
       c.recent_execs,
       CAST(c.history_avg_us / 1000 AS decimal(12, 1)) AS history_avg_ms,
       CAST(c.recent_avg_us  / 1000 AS decimal(12, 1)) AS recent_avg_ms,
       CAST((c.recent_avg_us - c.history_avg_us) * c.recent_execs / 1000000
            AS decimal(12, 1))                         AS extra_seconds_24h,
       LEFT(t.query_sql_text, 120)                     AS query_text
FROM cmp AS c
JOIN sys.query_store_query AS q       ON q.query_id = c.query_id
JOIN sys.query_store_query_text AS t  ON t.query_text_id = q.query_text_id
WHERE c.recent_avg_us > 2 * c.history_avg_us
  AND (SELECT COUNT(*) FROM sys.query_store_plan AS p2 WHERE p2.query_id = c.query_id) > 1
ORDER BY extra_seconds_24h DESC;

• extra_seconds_24h is the ranking that matters: a query that went from 2 ms to 8 ms but runs a million times a day costs more than a report that went from 1 s to 3 s once.
• Remove the plans > 1 filter to see queries that slowed down on the same plan. Those are usually waits (blocking, I/O, memory) or more data, not a plan choice, and forcing a plan won't help them.
• In SSMS, the Regressed Queries report under the database's Query Store folder shows the same comparison as a chart.

3. Compare the plans for one query

Take a query_id from step 2 and list every plan it has used, with when each was seen and how it performed.

DECLARE @query_id bigint = 42;

SELECT p.plan_id,
       p.is_forced_plan,
       p.last_compile_start_time,
       MIN(rs.first_execution_time) AS first_seen,
       MAX(rs.last_execution_time)  AS last_seen,
       SUM(rs.count_executions)     AS executions,
       CAST(SUM(rs.avg_duration * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) / 1000 AS decimal(12, 1)) AS avg_ms,
       CAST(SUM(rs.avg_cpu_time * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) / 1000 AS decimal(12, 1)) AS avg_cpu_ms,
       CAST(SUM(rs.avg_logical_io_reads * rs.count_executions)
            / NULLIF(SUM(rs.count_executions), 0) AS decimal(14, 0))        AS avg_reads,
       SUM(CASE WHEN rs.execution_type <> 0 THEN rs.count_executions ELSE 0 END) AS aborted_or_failed
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE p.query_id = @query_id
GROUP BY p.plan_id, p.is_forced_plan, p.last_compile_start_time
ORDER BY first_seen;

A clear regression looks like this: an older plan with low avg_ms and avg_reads, then a newer plan, first seen around the time the complaints started, with both numbers far higher.

Watch aborted_or_failed. Query Store counts executions the client cancelled (execution_type 3) and ones that ended in an error (4) separately from regular ones. Application command timeouts show up as aborted executions, so a bad plan often shows here first: the slowest runs are cut off at the timeout instead of finishing.

To see what changed, open both plans side by side:

SELECT plan_id,
       TRY_CAST(query_plan AS xml) AS plan_xml
FROM sys.query_store_plan
WHERE query_id = 42;

Click a plan_xml value in SSMS to open the graphical plan. If it returns NULL, the plan is nested too deeply for the xml type; save the query_plan text as a .sqlplan file and open that instead. Look for a seek that became a scan, a join type that changed, or a much lower row estimate on the first operator that reads the big table. Our guide on reading a SQL Server execution plan covers what each of these means.

4. Find out why the plan changed

Forcing the old plan stops the pain, but the cause is still there. Line up the new plan's first_seen and last_compile_start_time with what else happened.

SELECT s.name            AS stats_name,
       sp.last_updated,
       sp.rows,
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.last_updated DESC;

• Statistics updated just before the new plan, often with a small rows_sampled: the estimates changed. Updating with a larger sample, or FULLSCAN for that statistic, may bring the old plan back on its own.
• Plans alternate between two shapes as the day goes on: parameter sensitivity. The plan is right for the value it was compiled with and wrong for others.
• An index was dropped or rebuilt, or a deployment changed the schema: compare with your change log. A forced plan that uses a dropped index can't be used any more (step 6).
• The compatibility level changed: the new cardinality estimator or optimizer behaviour is the cause. Step 8 shows how to pin the old behaviour for one query.

5. Force the last good plan

When one older plan is clearly better and you need relief now, force it. SQL Server then tries to use that plan every time the query compiles.

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

To remove a forced plan:

EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

A plan can only be forced for the exact query text Query Store recorded. If the application builds SQL with literal values instead of parameters, each variation is a different query, and forcing a plan for one of them does nothing for the others.

6. Check the forced plan is used

Forcing can fail without an error to the application. When it does, SQL Server compiles the query normally, raises an Extended Event, and records the reason on the plan. The application just stays slow.

SELECT p.query_id,
       p.plan_id,
       p.plan_forcing_type_desc,          -- MANUAL or AUTO (SQL Server 2017+)
       p.last_force_failure_reason_desc,
       p.force_failure_count,
       p.last_compile_start_time,
       p.last_execution_time
FROM sys.query_store_plan AS p
WHERE p.is_forced_plan = 1
  AND p.last_force_failure_reason <> 0;

The usual reasons:

• NO_INDEX: the plan uses an index that no longer exists. Recreate the index or unforce the plan.
• HINT_CONFLICT or SETOPT_CONFLICT: the query now carries a hint, or runs with SET options, that the plan can't satisfy.
• NO_PLAN: the optimizer could not produce a valid plan matching the forced one, for example after a schema change.

On SQL Server 2016, remove the plan_forcing_type_desc line; that column arrived in SQL Server 2017.

7. Let SQL Server correct it

From SQL Server 2017, the engine looks for plan-choice regressions itself and records them in sys.dm_db_tuning_recommendations, whether or not you let it act. Check this view first: it may already have found the query you are chasing. Reading it needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

SELECT r.reason,
       r.score,
       JSON_VALUE(r.state, '$.currentValue')                  AS state,
       d.query_id,
       d.regressed_plan_id,
       d.last_good_plan_id,
       JSON_VALUE(r.details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations AS r
CROSS APPLY OPENJSON(r.details, '$.planForceDetails') WITH (
    query_id          bigint '$.queryId',
    regressed_plan_id bigint '$.regressedPlanId',
    last_good_plan_id bigint '$.recommendedPlanId'
) AS d
ORDER BY r.score DESC;

state is Active for a recommendation nobody has applied, Verifying while the engine checks a plan it forced, Success when it kept it, and Reverted when the forced plan turned out no better. This view is held in memory and is emptied on restart, so save what you need from it.

Automatic plan correction lets the engine apply these itself: it forces the last good plan, checks that the forced plan really is better, and unforces it if not.

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

SQL Server 2022 and later add per-query control. You can exclude a query from automatic correction, for example one whose plan you have forced by hand, or ask for a second regression check five minutes after a plan change, which catches queries whose slow executions only appear later.

-- Never auto-correct query 422
EXEC sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN', 'QUERY', 422, 'OFF';

-- Re-check query 537 five minutes after a plan change
EXEC sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK', 'QUERY', 537, 'ON';

SELECT * FROM sys.database_automatic_tuning_configurations;

Global trace flag 12656 applies the extended check to every query on SQL Server 2022 CU4 and later.

8. Fix it without pinning a plan

A forced plan freezes one shape. Often a better fix is to change how the optimizer treats that query and let it keep choosing. From SQL Server 2022, Query Store hints attach a query hint to a query_id without changing application code.

-- Parameter sensitivity: compile for the actual values each time
EXEC sys.sp_query_store_set_hints
     @query_id = 42,
     @query_hints = N'OPTION (RECOMPILE)';

-- Regressed after a compatibility level change: keep the older estimator for this query
EXEC sys.sp_query_store_set_hints
     @query_id = 42,
     @query_hints = N'OPTION (USE HINT (''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

• Setting hints again for the same query replaces the previous ones; it does not add to them.
• RECOMPILE costs a compile on every execution. Use it for queries that run seconds, not for ones that run thousands of times a second.
• If a hint would stop a plan from being produced, all hints for that query are ignored. Check sys.query_store_query_hints, which records the failure reason.

SELECT query_id, query_hint_text,
       last_query_hint_failure_reason_desc, query_hint_failure_count
FROM sys.query_store_query_hints;

EXEC sys.sp_query_store_clear_hints @query_id = 42;

The lasting fixes are usually outside Query Store: statistics maintenance that matches how the data changes, an index that serves every parameter value well, or a query rewritten so one plan suits all of them. Once one of those is in place, remove the forced plan or hint and confirm with step 3 that the query stays fast on its own.

The same steps in Quorum

Quorum reads Query Store for you and keeps plan history beside the workload, so a regression shows up as a change in a query's plan and timing across comparable windows, not as numbers you have to join by hand.

• Plan history and regressions from Query Store, with top queries grouped by fingerprint and compared against a baseline.
• Estimated against actual rows explained in plain English, so you can see why the new plan was chosen.
• Wait-time analysis for the same window, to tell a plan regression from blocking or I/O.
• Change timeline per connection, to line the new plan up with deployments and schema changes.
• Index advice ranked by measured impact, with storage and write overhead shown, when the lasting fix is an index.
• Grouped alerts with cooldowns; the notification opens the investigation for that query.