Comparing a Good and a Bad Plan for One Query in Query Store

Last week a query finished in one second, and now it takes a minute. Query Store can hold a good and a bad plan for the same query, along with runtime history. Compare the plans at the first meaningful difference before forcing either one.

Find the Query ID, Not Just a Similar Text

Query Store separates query text, query metadata, plans, runtime statistics, and time intervals. Start with a distinctive fragment of the SQL text, then confirm the full statement and its database context. Similar statements can differ by parameterization, SET options, or a single predicate. I do not compare plan IDs until I know they belong to the same query ID.

SELECT q.query_id, q.query_text_id,
       qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
  ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%YourDistinctiveTable%'
ORDER BY q.query_id DESC;

Replace the search text. Expect the search query itself in the result too, because Query Store captures it like any other statement. Query Store must be enabled and collecting the workload. If the query is absent, check capture mode, read-only state, and retention before declaring that the plan changed outside Query Store. The same text in another database has its own Query Store history.

Put Runtime Numbers on the Good and Bad Plan

A plan is not bad because its XML looks complicated. Compare duration, CPU, and reads in the same workload window. Runtime statistics are stored by plan and interval. The query below aggregates executions and weighted average duration for one query ID. The duration unit is microseconds, so divide by one million for seconds. Add a time-window filter in a real comparison.

DECLARE @QueryID bigint = 0;
SELECT p.plan_id, p.is_forced_plan,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_duration * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0) / 1000000.0
         AS average_seconds,
       MIN(i.start_time) AS first_interval,
       MAX(i.end_time) AS last_interval
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE p.query_id = @QueryID
GROUP BY p.plan_id, p.is_forced_plan
ORDER BY average_seconds DESC;

Replace zero with the confirmed query ID. The all-history average can mix quiet and busy periods, so compare intervals from last week and today separately. A plan used only for one unusual parameter can look terrible beside one used for thousands of ordinary calls. Check execution count and parameter values before calling the average a verdict.

Pull the Good and Bad Plan XML

Query Store stores the XML for each plan. Select the good and bad plan IDs from the same query and open each XML cell in SSMS. Save both .sqlplan files under clear names. The query below retrieves the XML and force status without changing anything.

DECLARE @GoodPlanID bigint = 0;
DECLARE @BadPlanID bigint = 0;
SELECT plan_id, query_id, is_forced_plan,
       force_failure_count,
       last_force_failure_reason_desc,
       query_plan
FROM sys.query_store_plan
WHERE plan_id IN (@GoodPlanID, @BadPlanID);

Confirm that the two rows share the intended query_id. A forced plan flag means someone already made an intervention; understand it before adding another. Plan XML can be large. Save the files while Query Store still retains them, and keep the runtime table beside them. The plan alone does not say when it was good.

Compare the Good and Bad Plan in SSMS

Open one saved plan in SSMS and use Compare Showplan to open the other. The side-by-side view highlights changed operators and estimates. Start at the first operator where estimated row counts or access paths diverge, moving from data access toward joins. A later Sort or Hash Match can be a consequence of an earlier estimate error. Fixing the later operator first can miss the cause.

I check whether one plan seeks an index and the other scans, but I do not stop there. A seek that reads millions of rows can be worse than a scan. Read actual rows, estimated rows, rows read, and logical reads. If the plan was captured only as an estimated Query Store plan, reproduce the query with an actual plan under representative parameters for runtime row counts.

Check Joins and Memory Grants

A shift from hash join to nested loops can be good for a small outer input and disastrous for a large one. Compare the estimated outer rows, actual rows, and lookup work. A shift in memory grant can cause spills or reduce concurrency. Check warnings for Sort and Hash Match spills, and compare granted memory with what the query used. The cause can be stale statistics or a different parameter at compilation.

Join order also changes when estimates change. Do not assume that the join icon itself is the problem. Find the input whose estimated size first changed, then ask why. A new filter, index, statistic, or implicit conversion can alter that estimate. The plan comparison is a map of consequences and causes, not a contest between pretty pictures.

Compare Parallelism and Settings

One plan can use parallel operators while the other stays serial. Check MAXDOP hints, database scoped settings, cost threshold, and NonParallelPlanReason when present. A parallel plan can have lower elapsed time and higher total CPU. Compare both metrics before deciding it is better for a busy server. Also check compatibility level and cardinality estimator differences if a deployment or restore changed the database environment.

A plan comparison has to hold inputs constant. Use the same query text, parameter values, data snapshot when possible, and relevant SET options. If you cannot reproduce the old data, say so in the investigation note. Query Store history still helps, but it does not make an old plan an automatic fit for today's distribution.

Decide on the Next Change

If the old plan remains good across representative parameters, a temporary Query Store force can stabilize the workload while you fix the cause. Verify that forcing succeeds and that runtime improves. If the old plan helps one parameter and hurts another, look at parameter-sensitive behavior, statistics, and indexes instead. A force is a control, not a diagnosis.

Re-run the report at peak and off-peak times if concurrency changes the outcome. A plan that looks good during an isolated test can request enough memory to queue other reports. Query Store gives per-plan runtime history, but you still need to connect that history to the application period users care about.

I finish with a short table: query ID, both plan IDs, time windows, execution counts, duration, CPU, reads, first estimate difference, and the change tested. That is enough for another DBA to repeat the reasoning. Without the numbers, a Compare Showplan screenshot is interesting but does not prove the workload improved.

Which operator first diverged in the slow plan, and what changed in the data it read?

Related reading on this blog: Create Efficient Query Plans Using Query Store: Analyzing SQL Server Query Plans: Part 3 and Top Resource Consumers in Query Store.

A different plan is not the answer by itself, it is evidence when the change explains measured slowdown.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.