Query Store ships GB-scale plan XML on catch-up and re-ships hot plans every cycle - add a per-database plan_id watermark

[erikdarlingdata]

The third finding from the prod-sql-use2 field investigation, filed as a measured-decision marker per the reporter's own recommendation: NOT worth acting on blind.

The Query Store collector is already defended against the below-TOP TVF defect its siblings had (its expensive payloads are catalog COLUMNS via 1:1 joins, plan text dedupes to one copy per plan_id via the ROW_NUMBER CASE that composes with the TOP by design, and the @cutoff_time watermark bounds each cycle). The one theoretical exposure: the ROW_NUMBER window forces the joins plus the rn=1 plan-text conversion to evaluate for ALL qualifying rows BEFORE the TOP - so a pathological burst converts XML for every distinct plan in the window, including rows the TOP discards.

Burst-only, and unvalidatable on the reporting fleet (replicas serve zero QS rows). Tripwire: when a QS-BEARING store shows query_store sweep times out of family with its row counts, measure this exposure there first - the reporter's ab_test.py methodology (interleaved pairs, warmup discarded, row-identity parity) is the template. Do not restructure the QS read speculatively; its current shape's properties (dedup-compose-with-TOP) are load-bearing and pinned.

[erikdarlingdata]

The measure-first condition this issue was filed under has been MET, faster and better than expected: the field engineer realized the replicas carry the primaries' Query Store catalogs in full (database-resident, replicated - the collector skips them only because the data DESCRIBES the primary), and ran the exact collector query - all SQL2022 fragments, the rn=1 plan-text CASE, the TOP 50000 backstop, the LOOP JOIN hint - against a replicated catalog of 1.33M runtime_stats rows / 165K plans / 150K queries.

Measured

arm	time	payload
steady state (cutoff now-6min)	457-472 ms, 0-4 rows	fine
fresh-DB fallback (cutoff -60min)	45.5-51.1 s, ~9,050 rows	~1,330 MB
24h catch-up clamp	81,710 qualifying rows	NOT executed (would blow the 50k backstop; multi-GB)

Payload attribution on the 60-minute window (server-side aggregate, nothing shipped): plan XML deduped once per plan = 1,227 MB - 92% of everything (5,329 distinct plans, ~230 KB average); query text repeated per row = 32 MB, and deduping it like plan text saves only 9 MB - text is a non-issue. The rn=1 dedup works exactly as designed; the cost is the genuine XML volume of the hot working set.

Three implications, upgraded from theory to findings

1. Onboarding breaks on big tenants: a fresh database's first collection at this scale runs 45-51 s - past the 30 s command-timeout class - so the fresh-DB path may abort and retry-loop. Sub-question to CHECK rather than assume: whether SqlClient's CommandTimeout kills a streaming read mid-fetch the way the reporter's pyodbc run died.
2. The 24 h catch-up clamp bounds ROWS but not BYTES - the Collectors: bound query_store memory — byte budget, per-database flush, OOM semantics (#1556) #1557 lesson (row caps do not bound bytes) applying to the payload path: 82K qualifying rows on one tenant is a multi-GB cycle if it ever runs.
3. Steady state on primaries (reasoned from code; in-memory QS slices do not replicate, so a replica cannot show this): the interval-grain HAVING re-qualifies the open interval whenever a plan sees new executions, and the rn=1 dedup is per-CYCLE - so a continuously-hot plan re-ships its ~230 KB XML every cycle, forever. The store's content-digest dedup absorbs it at rest; the wire and the collector pay it every cycle. Order of magnitude: hundreds of hot plans x 230 KB per cycle per busy tenant.

Fix direction (the reporter's, and it is grounded in QS semantics)

Plan XML for a given plan_id is IMMUTABLE, and plan_ids are monotonic. A per-database @max_shipped_plan_id watermark - the same machinery as the existing @cutoff_time - makes the plan-text CASE ship XML only when plan_id exceeds the watermark AND rn=1. Steady state then ships XML only for genuinely-new plans; onboarding still pays once, knowingly, or gains a numerics-first option / byte-aware cap.

Design weight to respect at build time

• Shipping NULL XML for a known plan must still resolve through the query_text/query_plan_xml stored inline per row: 94% of a field store — normalize into hash-keyed dimension tables (~135x measured) #1767 dimension pipeline: the row needs its plan identity/digest reference WITHOUT carrying the text, and the store must treat NULL-with-known-plan_id as 'unchanged', never as 'plan text lost'. That semantic is the real work; the SQL change is small.
• The watermark is per-database per-server state beside the cutoff, in both apps, surviving restarts (the [BUG] Webhook (Teams/Slack) alerts re-fire after an app restart - #981 restart-dedup was only applied to the email channel #1145-style seeding question applies).
• Byte-aware capping for the catch-up path is its own arm and should not be folded in silently - it changes what a catch-up cycle delivers and belongs beside the Collectors: bound query_store memory — byte budget, per-database flush, OOM semantics (#1556) #1557 budget machinery.

Field scripts: qs_test.py + qs_payload_split.py alongside the earlier capture/ab harnesses.

[erikdarlingdata]

Design direction update after discussion. The watermark remains the core fix, and two decisions are now folded in: shipping is BYTE-budgeted per cycle per database (the field's 1.33 GB fresh-DB pull becomes a bounded multi-cycle catch-up instead of one timeout-prone pull; no knob, default derived from the field numbers), and a phase 2 grows out of it - a separate lower-stakes backfill worker, since Query Store genuinely holds historical data that first contact currently discards (today's behavior, verified in source: 60 minutes back on first contact, 24-hour clamp after outages). The worker walks newest-first from the live watermark's floor, serves both onboarding backfill and post-outage catch-up with one mechanism, runs on its own budgeted tick so it cannot delay the main cadence, and is staged at the raw-retention horizon first - deeper backfill interacts with rollup routing and is deliberately a separate later decision. Phase 2 gets its own issue at build time; this issue stays the watermark. Full plan doc is local; build follows a plan-review pass.

[erikdarlingdata]

Reconstructing the lost plan doc from the surviving design-direction comment above, the original thinking (recovered), and tonight's source verification. This is the phase-1 implementation plan for the pre-build review pass.

Verified in source tonight

QueryStoreCollector.BuildCutoffParameters confirms the comment's claims exactly: @cutoff_time = ClampCatchup(watermark) ?? collection_time − 60min — so first contact takes 60 minutes and discards the rest of a ~30-day catalog, and post-outage catch-up is clamped to 24h (WatermarkPolicy, the #1556 incident fix), with the clamp logged. The doc there also pins the DATA_FLUSH_INTERVAL burst cadence as source behavior, not a bug. Field numbers from the July replica measurement stand: a fresh QS-bearing catalog is ~1.33 GB across ~165K plans — the single-pull failure mode this issue exists for.

Phase 1: byte-budgeted shipping on the existing watermark

1. The budget is host-side, not query-side. The collector's read shape (dedup-composes-with-TOP, rn=1 plan-text CASE) is load-bearing and pinned per this issue's own warning — don't restructure it. Instead the host's row loop accounts plan-XML bytes as it reads and stops the cycle when the per-database budget is spent.
2. The watermark advances only to the boundary actually shipped. This is the invariant that makes a budget cut safe: next cycle resumes exactly where this one stopped, so a cut never opens a silent hole (the failure the 24h clamp knowingly accepts and logs — the budget must be better than the clamp, not another hole-maker). Needs one design check during build: whether the write path's upsert keying makes partial-interval re-ship idempotent; if yes the boundary can be coarse (interval), if no it must be exact.
3. Direction: forward from the watermark (oldest-first) on the live path, preserving arrival-order continuity for the rollup/CAGG refresh windows. Newest-first walking belongs to the phase-2 worker, which serves the "most recent history is most valuable" onboarding case.
4. Default derived, no knob: from the field numbers, ~64 MB/cycle/database turns the 1.33 GB fresh-catalog pull into ~21 bounded cycles (~1¾ hours at the 5-minute cadence) instead of one timeout — same envelope as a large plan-XML cycle the store already absorbs routinely. Stated here for the review pass; trivially adjustable before build.
5. Operator visibility: a budget-cut cycle logs like the clamp does (database, bytes shipped, boundary reached), so a long catch-up is observable rather than mysterious.
6. Tests: pure pins for budget accounting + watermark-advance semantics (the never-a-hole invariant); a gated live test that plants an oversized backlog and proves multi-cycle convergence with row-identity parity across the cut.

Phase 2 (its own issue at build time, per the direction comment)

The lower-stakes backfill worker: walks newest-first from the live watermark's floor, one mechanism for both onboarding backfill (today's discarded ~30 days) and deep post-outage catch-up, on its own budgeted tick so it can never delay the main cadence, staged at the raw-retention horizon first — deeper backfill interacts with rollup routing and stays a separate later decision.

Build starts on this plan after your pass (or on your silence + the standing serial order, whichever comes first tomorrow).

[erikdarlingdata]

Phase 1 shipped in #2023 (merged to dev): Query Store ships oldest-first on the existing per-database bounds, with watermark-exact resume — a bounded cycle now defers its backlog instead of dropping it, TOP ... WITH TIES + client tie-group completion keep the boundary exact against the strict cutoff, the text budget drops 256→64 MB (cuts cost latency, never data), and both hosts' bounded-cycle WARNINGs report MB shipped + boundary reached. The 60-minute first-contact window and 24h clamp are untouched — backfilling the ~30 days behind them is phase 2, tracked in #2022 per the plan.