[BUG] query_store collector: monolithic TVF join is a fixed ~30s+ cost on big catalogs — stage through #temp (sp_QuickieStore architecture)

[erikdarlingdata]

Field evidence from the dogfood fleet (2026-08-08): after #2125's adaptive shrink deployed, most stragglers converged, but a hard core (echo@multi22, oak@multi-01, Surge@multi32, spruce@columbia, insa@multi-49, Dusk@multi22, and rotating peers) kept failing at every window width — 60m, 30m, 15m identically — with zero rows shipped for hours. Width-independence was the tell: some fixed cost eats the 60s command timeout before the window predicate matters.

Bisection (echo@multi22, SQL 2022 16.0.4215.2, 3 GB QS catalog, 82k plans / 64k queries / 526 intervals, all probes 30s-capped, read-only)

probe	result
current interval pre-filter (runtime_stats scan on last_execution_time)	426 ms — not the wall
interval-catalog pre-filter (runtime_stats_interval.end_time)	20 ms
raw rows in last-hour intervals	10,292 rows, 252 ms
inner aggregate alone (GROUP BY plan_id, interval_id)	7,346 groups, 81 ms
bare SELECT COUNT(*) sys.query_store_plan	82k rows, 319 ms
bare SELECT COUNT(*) sys.query_store_query	64k rows, 208 ms
aggregate JOIN qsp JOIN qsq (the collector's shape, WITH the LOOP JOIN hint)	timeout ≥30s
same WITHOUT the hint	timeout ≥30s
aggregate joined to qsp ONLY	timeout ≥30s
staged: aggregate INTO #x, then #x JOIN qsp JOIN qsq	stage 56 ms + join 409 ms = 524 ms total

Two relations that each materialize in well under half a second join catastrophically when the optimizer has only TVF fixed-guess cardinalities to work with — the inner side re-materializes per probe (7k probes × ~300 ms ≈ 35 minutes of work against a 60s timeout). The OPTION(LOOP JOIN) hint neither causes nor cures it.

Fix

Restructure QueryStoreCollector.BuildPayloadBody to stage the runtime-stats aggregate into a #temp and run the plan/query/text joins FROM the temp — sp_QuickieStore's architecture, for exactly this reason. Constraints to preserve:

• ONE result set at the end of the batch (the reader/byte-budget machinery is single-result-set);
• TOP WITH TIES + derived-watermark semantics unchanged on the final SELECT;
• the LOOP JOIN hint must NOT survive onto the staged join (looping from the temp into the TVFs is the pathology itself; the 409 ms join is unhinted);
• both engine arms (on-prem/RDS sp_executesql nesting and the Azure per-database parameter path — #temp scope lives and dies with the batch in both), both SKUs, live + backfill variants;
• while here: the interval pre-filter can resolve ids from sys.query_store_runtime_stats_interval (20 ms) instead of scanning runtime_stats (426 ms) — not the wall, but strictly better and the same tie-group semantics via the existing HAVING.

Expected field effect: the wedged members drop from 'never completes inside 60s at any width' to sub-second, and the adaptive shrink becomes a rarely-needed safety net instead of a perpetually-pinned floor.

Evidence gathered via read-only probes from the monitor box; full transcript in the session that produced #2125/#2132.

[erikdarlingdata]

Fixed on dev by #2134. The payload stages its slice aggregate into a temp table and joins the plan/query/text catalogs FROM it — real cardinalities instead of TVF fixed guesses, each catalog scanned exactly once, both statements under OPTION(RECOMPILE), the old LOOP JOIN hint gone and pinned absent. Field-validated on the wedged store before merge: staged core 524 ms, the full 55-column batch with plan capture cleared a one-hour backlog in 21.3 s where the old shape never finished inside the 60-second command timeout. Both SKUs and both engine arms through the one shared body. Deploying to the dogfood box next — the acceptance test is watching the wedged members (insa was 40 consecutive failed cycles deep) converge.