A 90-second plan-flip incident cannot page: timely findings cap below notify_severity, and the finding that clears 1.5 waits on query_store cadence #3467

The measured incident

2026-09-15, one production store class on a multi-tenant server. A parameterized statement that joins a #temp table flipped to a plan doing ~35x the logical reads (baseline 2.8 ms / ~300 reads per execution; regressed ~3.9 s / ~60K reads averaged, 287K logical reads observed live on one in-flight execution). Every caller shares the parameterized plan, so all tenants inherited it at once: concurrent executions piled up on PAGEIOLATCH_SH, physical reads saturated the volume, and 16 executions across ten tenants hit the client's 30-second command timeout together. The whole episode ran ~90 seconds and self-cleared when the plan churned out.

What the store knew, and when

The collection side was ahead of the incident:

• 20:10:31Z — active-query snapshot captured the pileup live: three concurrent copies of the statement, one at 29 s elapsed / 1.3 s CPU / 287K logical reads, two suspended on PAGEIOLATCH_SH. The same statement's 20:07:01Z snapshot shows 54 ms / 8.2K reads.
• 20:10:34Z — waiting-task sample landed during the death second, including a tempdb WAIT_ON_SYNC_STATISTICS_REFRESH (plausibly the recompile trigger itself).
• query_store stats arrived on collection cadence; the regression (plan count 2 → 4, the duration/read blowup) was queryable by ~20:38Z.

So evidence sufficient to describe the incident existed in the store while the incident was still running. What follows is about the analysis/notify path, not collection.

What analysis produced, and when

• Scheduled run 20:31:48Z (~21 min after): ANOMALY_QUERY_DURATION severity 0.72, ANOMALY_BATCH_REQUESTS 1.0, ANOMALY_CPU_SPIKE 0.56. Correct shape, but every one of them sits below the default notify_severity of 1.5.
• On-demand run 20:38:49Z: PLAN_REGRESSION at severity 1.5 — notify-eligible — with the force-plan drill-down. This finding could not exist earlier because it keys off query_store data that had not landed yet.

Alerting on this store happened to be muted by operator choice at the time; that is not this issue. The issue is that with everything switched on, nothing pages until query_store collection catches up: for a short, self-clearing plan flip, the notify-eligible finding trails the user-visible damage by ~30 minutes, and the findings that ARE timely cap at 1.0 and cannot clear the threshold.

Separately, none of the alert families covers the shape either: long_running_query is 30 minutes, the victims died at 30 seconds; poison_wait doesn't class PAGEIOLATCH; blocking and deadlocks don't apply; CPU stayed ~50%. The scheduled-analysis anomalies are the only automatic catcher for this class, and they can't notify.

Proposed starting shape (one rung)

Synthesize a notify-eligible finding from the two collectors that were already current during the incident: same-statement pileup — N concurrent sessions in the active-query snapshot on the same statement text/hash, all past X seconds elapsed, with IO-class waits, where the same statement's recent snapshots show sub-second executions. Severity scaled by session count x elapsed (the #3462 principle: magnitude, not just presence). That finding was computable at 20:10:31Z from data the snapshot collector had already written.

Alternatives considered, for the record:

• Magnitude-scaled severity on ANOMALY_QUERY_DURATION / ANOMALY_BATCH_REQUESTS so a mass-timeout window clears 1.5. Cheaper, but it stays on the 30-minute analysis cadence, which gives back most of the win for a 90-second episode.
• Lowering notify_severity. Rejected: that trades this gap for noise, and Anomaly findings fire at 0σ against healthy baselines - memory pressure reports "spiked to 100% - 0σ above its 100% baseline" #1996 already documents what under-qualified anomaly findings do to trust.
• Accepting the latency because client-side timeout monitors (outside this tool) page first anyway. That is the status quo; this incident was in fact surfaced by one. It leaves this tool describing incidents it watched happen.

Prior art: #693 (anomalies as analysis input), #1996 (anomaly severity quality), #3462 (materiality/magnitude scaling precedent), #2138 (force-plan bot — remediation-side, and it would want exactly this class of timely detection as its trigger input).