The Plan Got Worse. Query Store Had the Previous One.

A SQL Server query can run well and then suddenly become expensive after the optimizer chooses a different execution plan. The trigger might be a statistics change, recompilation, a schema or index change, a compatibility-level change, a shift in data distribution, parameter sensitivity, or another optimizer input.

The important point is that the query text does not have to change. Same query, same feature, different plan, much more CPU.

At small scale, a DBA can inspect the plan cache and intervene. Across hundreds of databases, that operating model does not scale.

Why Query Store changes the investigation§

The plan cache is useful, but it is a snapshot. Query Store gives the database a historical record of query texts, plans and runtime statistics. That changes the question from what is expensive now? into which plan was good, when did behaviour change, and how does the new plan compare with the one before it?

During one observed window across a large SQL Server estate, the tuning data looked like this:

PLAN-REGRESSION RECOMMENDATIONS

~1,900

DATABASES INVOLVED

~270

EST. CPU AVOIDED

~1.5 CPU-hrs

Those values are deliberately rounded. The more important signal was frequency — regressions were not rare one-off events.

One regression made the mechanism obvious§

One high-confidence recommendation looked approximately like this:

GOOD PLAN   avg CPU ≈ 336 ms
BAD PLAN    avg CPU ≈ 2,015 ms
REGRESSION  ≈ 6x CPU per execution

Across the executions represented by that recommendation, SQL Server estimated roughly thirteen minutes of avoidable CPU time. There were more extreme regression factors elsewhere in the data, including cases hundreds of times worse than the prior plan — but an extreme ratio does not automatically mean a query dominated estate CPU. Execution frequency still matters:

impact = cost_delta_per_execution × execution_count

The self-healing part§

Query Store provides the history. Automatic tuning provides the control loop.

ALTER DATABASE [YourDatabase]
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

SQL Server can identify a plan regression and force a previously known good plan:

That last step matters. This is not the same as permanently pinning every plan somebody once liked — automatic plan correction monitors the result and can stop forcing if the action does not provide the expected improvement.

Verification§

Feature state:

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

Detected recommendations and actions:

SELECT
    reason,
    score,
    JSON_VALUE(state, '$.currentValue') AS current_state,
    JSON_VALUE(state, '$.reason') AS state_reason,
    details
FROM sys.dm_db_tuning_recommendations;

A different system, eighty-one recommendations§

That is the query. This is what it returned on a different system — another product, another database, with FORCE_LAST_GOOD_PLAN enabled — captured whole rather than sampled.

It is not a zoom-in on the estate above, and none of these numbers belong to that engagement. That is what makes it worth including: the same technique, pointed at an unrelated workload, and the same shape comes back.

RECOMMENDATIONS, ONE DATABASE

81

ESTIMATED CPU, WHOLE SET

88,087 s

FROM THE TOP TEN

90%

Ranked by the export’s own estimate of CPU reclaimed:

The distribution is the finding. One query is 30% of the set. The top five are 76%. The top ten are 90%, which leaves the remaining seventy-one recommendations sharing a tenth of it — and twenty-one of those are worth less than a single CPU-second each, 3.7 seconds between them.

That is not an argument for ignoring the tail. It is an argument that a list of eighty-one items is not eighty-one pieces of work, and that any process which treats it as a queue to be worked through will spend most of its attention on nothing.

Score ranks confidence, not impact§

The score column is tempting to sort by, and it is the wrong key.

Query 25786035 scores 79 and is the fourth-largest saving in the set at 2.2 CPU-hours, earned by a mild 4.8x regression multiplied by 150,866 executions. Query 25642448 scores 99 on a 115x regression and is worth 5.3 seconds, because the query ran 579 times in total. Across the set the two agree only loosely: the eight recommendations scoring 100 do account for 74% of the estimated CPU, but a rank correlation of 0.67 is not something you can sort a work queue by.

Most regressions were caught within about twenty calls§

The regressedPlanExecutionCount column has a floor in it. Twenty-five of the eighty-one recommendations show exactly fifteen executions on the bad plan, and forty-one of eighty-one show twenty-one or fewer:

3 calls    2 recs
7 calls    1 rec
9 calls    1 rec
12 calls    1 rec
14 calls    3 recs
15 calls   25 recs   <- the floor
16 calls    3 recs
...
median across all 81 recommendations: 21 calls

A number that repeats twenty-five times across a set is a threshold rather than a coincidence: detection wants a minimum sample before it will call a plan regressed, and most of these were identified as soon as that sample existed. Query 21115105 went from 12 ms to 29 seconds per call and was flagged after fifteen of them.

This, not the CPU column, is what the control loop actually produces. The saving is a consequence of the latency — whatever a regression costs per call, the loop bounds how many calls pay it.

The exception is the more interesting row. Query 69 ran 10,108 times on its bad plan at 68.94 ms against a 0.15 ms baseline — 695 CPU-seconds genuinely spent, the largest real loss anywhere in this set, from a recommendation sitting thirteenth by estimated saving. The floor is where most regressions land, not where all of them do, and the ones that escape it are the ones worth a human.

CPU is not the only way a plan regresses§

Two recommendations — 25642447 and 25739102, both scoring 100 — carry a regressedPlanErrorCount of 1 against a recommended plan with none. The new plan did not only get slower. Worth checking that column before concluding a regression set is purely a performance story.

Query Store is a dependency§

FORCE_LAST_GOOD_PLAN depends on Query Store being available and writable. That means the operating model also has to answer whether Query Store is enabled intentionally, whether it is READ_WRITE, whether the capture policy is appropriate, whether the maximum size is bounded, what happens if it reaches its limit, and how long history should be retained.

For a short investigation I have enabled Query Store temporarily and explicitly treated “leave enabled or disable afterwards” as a follow-up decision. For estate-wide automatic tuning, Query Store becomes part of the remediation architecture, so its configuration has to be deliberate.

A change window is context, not proof§

In one investigation, performance work happened around a database that had also moved from an old compatibility level to a current one and had its indexes rebuilt. Those changes are relevant — they can affect plan compilation and optimizer behaviour — but they are not, by themselves, proof that the compatibility-level change caused a particular regression.

Query Store is useful precisely because it moves the investigation away from temporal coincidence and toward plan and runtime evidence.

Guardrails§

A formerly good plan can stop being good. Schema changes, index changes, major data-distribution shifts and workload changes can invalidate the assumptions that made an earlier plan efficient. A forced plan should therefore be observable, not forgotten.

Parameter sensitivity still exists. A plan that is excellent for one parameter distribution can be poor for another. Repeated regressions on the same query are a reason to investigate the query and data shape, not an excuse to rely on forcing forever.

Query Store itself needs limits. Capture without bounds and the diagnostic system becomes its own operational problem. Size, cleanup and capture policy are part of the feature.

Estimating avoided CPU§

The estimate is worth taking apart, because it is easy to quote as something it is not.

Reproducing the export’s own arithmetic against the eighty-one recommendations above gives an exact match, and the multiplier is the part that matters:

estimated_cpu_avoided
    =
(avg_cpu_regressed - avg_cpu_recommended)
    ×
(executions_on_regressed_plan + executions_on_recommended_plan)

The per-call delta is multiplied by every execution of that query on both plans — not by the executions that actually ran on the bad plan. So the figure is a projection: what this query’s whole observed call volume would have cost at the regressed plan’s rate, against what it costs at the recommended one.

That distinction is not academic. For this database:

ESTIMATE, WHOLE SET

88,087 s

CPU ACTUALLY SPENT ON BAD PLANS

4,054 s

RATIO

22x

Roughly 68 minutes of CPU was genuinely burned on regressed plans. The headline number is twenty-two times that, and the gap comes almost entirely from the queries the loop caught early: query 21115105 wasted 435 CPU-seconds across its fifteen bad calls, and contributes 26,474 seconds to the total because the other 897 calls in the window were what the projection prices.

The two figures in this note are also not comparable with each other, and not merely because one of them is rounded. The estate figures and this database’s 88,087 seconds come from different products on different systems, measured separately. They do not add up, and neither is a sample of the other. Whichever estimator a report uses, its definition belongs next to the number — and so does the system it was taken from.

Why this matters at estate scale§

The biggest benefit is not the CPU in any one sample — not the estate’s rounded 1.5 hours, and not the other system’s projected 24. It is reducing the time between a plan becoming bad and something acting on it, which is the number the eighty-one-recommendation set actually demonstrates: a median of twenty-one calls on the bad plan before it was identified.

Without automatic correction, that path runs through an alert or a complaint, a human investigation, identifying the plan, and then a mitigation. With Query Store and automatic plan correction, it runs through a historical comparison, a high-confidence recommendation, and a bounded automatic correction — leaving human investigation for the repeat offenders. That is a better division of labour.

Takeaway§

Query Store is often described as a troubleshooting feature. At scale, the more interesting use is as state for a reliability control loop. It remembers what “good” looked like. FORCE_LAST_GOOD_PLAN can act when the system deviates from that state. Because the action is observable and reversible, the automation is bounded rather than blind — which is the difference between a diagnostic feature and an operational safety mechanism.