[FEATURE] Auto force plan bot — detect bad Query Store plans (CPU-weighted), force with self-review, evict-first option

[erikdarlingdata]

From Erik's feature email ('Auto force plan bot', 2026-08-08/09). Faithful decomposition of the ask, then a proposed phasing.

The ask, verbatim requirements

1. Detection: find opportunities to force Query Store plans when a bad plan is detected. Signal = CPU + duration combined, CPU weighted stronger (duration is confounded by factors outside the plan).
2. Importance gate: prerequisites before acting — significant executions AND resource expenditure judge whether a query matters enough.
3. Self-review + regression ledger: post-force review phases, and tracking for plan forces that were not a net benefit.
4. Live-activity component: watch server activity for queries currently running hot; target them with DBCC FREEPROCCACHE (targeted, per-plan) so a newly forced plan takes effect immediately.
5. Evict-first strategy: optionally clear the bad plan FIRST and observe whether the optimizer picks a better plan before resorting to forcing.
6. Goal: parameter-sensitive plans, and queries adversely affected by a stats update.

What already exists to build on

• query_store_stats series: per-plan CPU/duration/executions over time, both SKUs — the detection substrate is already collected.
• PlanCorrectionCollector: sys.dm_db_tuning_recommendations + FORCE_LAST_GOOD_PLAN desired/actual state — the engine's own regression opinions, already shredded and stored.
• remediation_action_json precedent: findings that carry executable T-SQL.
• Active-query snapshots for the currently-hot component.
• House rule to honor: the bot's self-review may only unforce its own forces — operator-placed forces are never touched.

Proposed phasing (the write-access question is the product decision)

• Phase 0 — advise-only detection (no target writes; shippable alone): plan-regression scoring from the stored QS series — per (query, plan) compare candidate vs incumbent on CPU-weighted deltas, gated by executions × resource spend; classify parameter-sensitivity (plan alternation) vs stats-update regression (correlate with stats timestamps); surface as findings with the exact sp_query_store_force_plan / DBCC FREEPROCCACHE(plan_handle) remediation attached.
• Phase 1 — evict-first, operator-armed: one-click/one-call execution of the targeted eviction with before/after observation windows, still human-initiated.
• Phase 2 — the bot: opt-in per server; evict-first then force-if-still-bad; post-force self-review windows; auto-unforce + not-a-net-benefit ledger; cooldowns and blast-radius caps (max actions/window); full audit trail. Requires a write-capable credential tier the product currently doesn't ask operators for — deliberate opt-in surface.

Phase 0 is unambiguous and read-only; starting there. Phases 1–2 want a design pass (credentials, gating UX, review-window arithmetic) before code.

[erikdarlingdata]

Phase 0 gap analysis — what exists vs the email's spec

Recon of the existing PLAN_REGRESSION machinery (PgFactCollector.QueryPerf.cs + FactScorer/FactRemediation; Lite has the DuckDB twin):

Already built:

• Execution-weighted cpu_per_exec AND dur_per_exec per plan over a 14-day QS window, per replica, plan-hash-deduped (recompiles under new plan_ids collapse).
• Latest-active-plan vs best-known-plan comparison, NULL-safe across non-AG installs.
• regression_factor ≥ 2 threshold, ≥25-execution floor, top-20 cap, forced-plan awareness (is_forced_plan, force_failure_count already ride the fact).
• Remediation generation for the finding, and parameter-sensitivity co-firing hooks.

Gaps vs the email, in Phase 0 scope:

1. CPU is NOT weighted stronger — the score is GREATEST(cpu_ratio, dur_ratio), so a duration-only regression (blocking, IO waits — exactly the confounders the email distrusts) fires at full strength. Proposed: primary signal cpu_ratio ≥ 2; duration corroborates but cannot fire alone unless extreme (e.g. dur_ratio ≥ 4 with cpu_ratio ≥ 1.25) — concrete weighting below is a default I'll pin in tests, tune to taste.
2. No resource-expenditure gate — the ≥25-exec floor is count-only; a trivially cheap query qualifies the same as a monster. Proposed: add a total-CPU-spend floor (execs × cpu_per_exec) so importance = volume × weight, per the email.
3. No stats-update classification — nothing distinguishes parameter-sensitivity (plan alternation with divergent per-plan costs — partially covered by the co-fire hooks) from a stats-update regression (a clean plan-change point with sustained worsening). Needs a change-point look at the interval series we already store.

Phases 1–2 (all new, design review wanted before code): evict-first via targeted DBCC FREEPROCCACHE(plan_handle), live-hot targeting from active-query snapshots, post-force self-review windows, the not-a-net-benefit ledger (auto-unforce applies ONLY to bot-placed forces — operator forces are never touched), per-server opt-in + write-credential tier, blast-radius caps.

Next: implementing gaps 1+2 (scoring + gate — small, test-pinned, advise-only so tunable) unless a different weighting is preferred; gap 3 after.

[erikdarlingdata]

Phase 0 (gaps 1 + 2) is up as #2139: CPU-primary scoring (duration-only regressions no longer fire; extreme-duration >= 4x needs >= 1.25x CPU corroboration and scores at half the ratio) plus the 10-CPU-second absolute-spend noise floor — applied identically to the fact and BOTH drill-downs (the drill-downs had their own copy of the old GREATEST and could surface rows the fact never counted).

Still on this issue after #2139 lands:

• Gap 3: classify stats-update-fixable vs parameter-sensitivity cases in the remediation text (the existing parameter_sensitivity_cofired hook is the seam).
• Phases 1–2 (any actual forcing: evict-first, live-hot targeting, self-review windows, not-net-benefit ledger, auto-unforce of bot-placed forces only, per-server opt-in, write-credential tier) stay design-gated pending your call on the write-credential model.

[erikdarlingdata]

Gap 3 design (posting before building — one scope change from the original framing)

Recon finding first: the parameter_sensitivity_cofired hook I pointed at in the gap analysis turns out to be a deprecated-Dashboard-only seam — FactRemediation reads it, but only the deprecated SqlServerDrillDownCollector ever wrote it (the whole abnormal_cpu_plans/CLEAR_PLAN flow is dormant in Lite and Darling). So the live-SKU classification gets built fresh, per-target and data-driven rather than per-analysis:

1. Per-row parameter_sensitivity_cofired in the regressed_queries drill-down (both SKUs). Query Store rows carry query_hash, and the PARAMETER_SENSITIVITY detector's signature lives in v_query_stats keyed by (database, query_hash, plan_hash) — both stores have it. The flag: this regressed query's hash has a latest plan-cache row showing the PSP signature (worker ratio >= 10 with the detector's exact floors: min >= 10ms, max >= 250ms, execs >= 20 — or grant ratio >= 5, or spill divergence). Same thresholds as the detector, so the flag can't disagree with the fact that would have fired.

2. Remediation steering in the force-plan preview (the #1882 replica-disclosure pattern: extra comment block, byte-stable when the flag is absent so the golden test stays meaningful). When flagged: names the plan-cache variance evidence; says what forcing does under PSP (pins one shape for ALL parameter values — the "regression" may just be the other shape's parameter population); offers the gentler first levers (UPDATE STATISTICS on the involved tables and watch for re-flip; on 2022+, Query Store hints / PSP feature); and states the bot consequence — Phase 1+ will not auto-force a flagged target, it gets an investigate verdict.

3. The scope change: I'm not building a "stats-update-fixable" classifier. We don't collect stats-update timestamps, rowmodctr, or sys.dm_db_stats_properties — classifying a regression as "stats will fix it" without that evidence would be invented confidence, which is exactly what Phase 0 just spent a review cycle stamping out of regressed_dimension. If we want a real stats classifier it needs a small collector for stats staleness first (happy to file that as its own issue). The honest version today is the PSP caution + gentler-lever text above.

Building 1+2 next unless you'd rather keep the stats half in some form.