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

Field evidence from the dogfood fleet (2026-08-08): after #2125's adaptive shrink deployed, most stragglers converged, but a hard core (AppDatabaseThirteen@multi22, AppDatabaseFourteen@multi-01, AppDatabaseFifteen@multi32, AppDatabaseSixteen@psi, AppDatabaseOne@multi-49, AppDatabaseTwelve@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 (AppDatabaseThirteen@multi22, SQL 2022 16.0.4215.2, 3 GB QS catalog, 82k plans / 64k queries / 526 intervals, all probes 30s-capped, read-only)

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.