Scenario

A finance reporting procedure that had run for months in ~45 seconds suddenly began taking 20–30 minutes, delaying the morning numbers. No deployment had changed the procedure, and the data volume had grown only modestly.

Symptoms

Same procedure, same parameters, dramatically longer runtime starting one specific night.
No code change in source control for the affected object.
Duration variance was bimodal: fast on some runs, very slow on others.

How We Analyzed It

Each step maps a concrete question to the module and signal used to answer it — all read-only against production.

1
Query Statistics
Opened the procedure in Query Store and compared plans over the regression window.
Signal: Two distinct plans for the same query_id — a fast seek plan and a slow scan plan.
Query Statistics: plan comparison across the regression window. (Placeholder image.)
2
Query Statistics
Correlated the plan flip timestamp with statistics updates.
Signal: The slow plan first appeared right after an auto-stats update on a skewed column.
3
Index Advisor
Checked whether a supporting index would make the good plan stable.
Signal: An existing index was usable but not chosen under the new cardinality estimate.

Evidence

Evidence — Plan flip

Query Store recorded plan_id 7 (45s, index seek) and plan_id 19 (24 min, full scan with a large hash spill) for the same query_id.

Evidence — Trigger

The regression timestamp lined up with an auto-stats update on a heavily skewed status column, which pushed the optimizer toward the scan plan.

Tip

When two plans exist for one query, the fastest non-destructive fix is often forcing the known-good plan while you address the root cause.

Query Statistics top-impact query detail with runtime metrics — Top-impact query detail and runtime trend. (Placeholder image.)

Recommendation

Force the known-good plan from Query Store to stop the bleeding immediately, then address the cardinality misestimate so the good plan wins on its own.

Force the fast plan (plan_id 7) for the affected query via Query Store.
Refresh statistics on the skewed column with a fuller sample so estimates improve.
Re-evaluate after the next data cycle and unforce once the optimizer reliably picks the seek plan.

-- Stop-the-bleeding: force the known-good plan (review IDs from Query Store first).
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- Root cause: improve estimates on the skewed column.
UPDATE STATISTICS dbo.Invoices (IX_Invoices_Status) WITH FULLSCAN;

-- Later, once the good plan is chosen naturally:
-- EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

Note

The application never applies changes automatically. Every script above is an example to review and run under your own change-control process.

Outcome

Forcing the plan restored runtime within minutes of being applied. After refreshing statistics, the optimizer chose the seek plan on its own and the forced plan was removed.

Report runtime

20–30 min→~45 s

Hash spills to tempdb

Large→None

Time to mitigate

—→< 10 min (plan force)

Modules used in this case

Query Statistics Index Advisor

Back to Use Cases Download