query_store collector costs 40-110s per run on multi-tenant primaries — the read has no interval watermark, so every cycle pays for the whole window

[erikdarlingdata]

Filed off today's fleet re-architecture (#2296 close-out), which made this cost visible per-server for the first time. All numbers are from collection_log on the two prod boxes, 2026-08-15 → 2026-08-17.

The measurement

The query_store collector costs 40–110 seconds per run on the big multi-tenant primaries, around the clock, and always has — it was buried in fleet averages and attributed to cross-region latency until today's split put the same servers on an in-region box:

• multi-53, hourly averages from use2's history (cross-region era): ~83–107s all Friday and overnight; 16–27s in the hours right after the [BUG] 3.4.0 query_store collector runs 37–100 min on Azure SQL DB (3.3.0 median: 4.8 s) — starves all other collectors #2150 text-flip deploy on 8-16 13:00 (a 4–6x cut); re-inflated to 65–78s through Sunday as Query Store content re-accumulated.
• Same server, in-region on use1 this evening: 43–53s — ~25% better than its same-day cross-region baseline. Latency was ~a quarter of the story; the read itself is the rest.
• Fleet-wide on use1 (42 primaries, Sunday evening): query_store avg 23.0s/run — the heaviest collector by 2x (plan_correction 10.8s, procedure_stats 5.5s, query_stats 2.9s). The trend across the evening rose 13.5s → 26.1s with flat rows per run (~6–10k) and trivial store-side time (~400 ms) — pure SQL-side read cost tracking workload, not backfill and not the Postgres write.
• The replica twin of the same collector reads 33 ms median on use2 — Query Store is primary-side; the replicas have nothing to serve, which is what isolates the cost to the QS DMV read itself.

The consequence

A 40–100s collector cannot fit its 60s cadence: the collection body outlives the sweep, relaunches are skipped, and the server collects at a multiple of the configured interval — #2296's arithmetic, now reproduced without cross-region latency. use1 logged 31 skip-relaunch warnings in its first loaded hour. Once the next install lands, #2308's sweep_pressure will read SATURATED/AT_RISK on the heavy primaries from exactly this number.

Mechanism hypotheses to verify (in order)

1. The runtime-stats read has no interval watermark. QS aggregates runtime stats per interval, and a closed interval is immutable — but the collector re-reads its whole window every cycle at full price. The same discipline that fixed plan fetch ([PERF] QS collector drain-mode passes cost the full byte budget every cycle — make the budget a knob, skip already-shipped plans by hash #2164) and text fetch ([BUG] 3.4.0 query_store collector runs 37–100 min on Azure SQL DB (3.3.0 median: 4.8 s) — starves all other collectors #2150) — fetch once past a watermark, never re-ship — should apply to closed intervals, leaving only the open interval's delta as the per-cycle cost.
2. Per-database fan-out. Query Store is per-database and these primaries carry dozens of tenant databases; if the read iterates databases serially, per-server cost scales with tenant count regardless of activity.
3. The TOP-N sort ([BUG] 3.4.0 query_store collector runs 37–100 min on Azure SQL DB (3.3.0 median: 4.8 s) — starves all other collectors #2150's measurement): choosing shipped rows still materializes every qualifying row's columns through the sort per database per cycle.

Interim mitigation available today (operator call)

config_collector_schedules can set query_store to a 5-minute cadence (per-server for the heavies or fleet-wide on the primaries box) — QS interval aggregation means minute-level collection mostly re-reads unchanged data, so the grain loss is small relative to 60→300s. One store row, live within a sweep, reversible. Not applied yet.

What resolves this issue

The collector-side fix (likely 1 + revisiting 3): per-interval watermarking so steady-state cost is proportional to NEW intervals, with the measurement above as the before/after yardstick — multi-53 at ~50s/run is the acceptance test, and the post-#2150-dip shows what a 4–6x cut looks like when real.

[erikdarlingdata]

Code-level analysis done; it corrects this issue's hypothesis 1, so stating that first: there is no missing closed-interval watermark. The live path's HAVING MAX(qsrs.last_execution_time) > @cutoff_time (with @cutoff_time = the store's MAX(last_execution_time) per database) already excludes any closed interval that was fully collected — a closed interval's newest execution can't postdate the watermark. The i.end_time > @cutoff_time pre-filter (#2133) narrows the id-resolution the same way.

The actual mechanism

The cost is the OPEN interval, re-aggregated in full every cycle, by design. Query Store rows are cumulative per-interval snapshots; the collector's own doc (QueryStoreCollector.cs:128–137) states it re-fetches the open interval every cycle so read-side rn = 1 can collapse to the latest snapshot. Consequences on a big multi-tenant primary:

• Per database, every 5-minute cycle re-scans and re-groups all flushed slices of the current interval (default 60-min intervals → ~12 re-aggregations per interval, each over more rows than the last). Summed over dozens of tenant databases per server, that's the 20–100s — and interval staggering across databases is why the curve is smooth rather than sawtoothed.
• The TOP (50000) WITH TIES sits on the final SELECT, after the #pm_qs_slice aggregate — it bounds shipping, never the server-side aggregation. [PERF] QS collector drain-mode passes cost the full byte budget every cycle — make the budget a knob, skip already-shipped plans by hash #2164 already measured this: cutting the byte budget 5x moved the batch clock not at all; the cost lives before the first row.
• Each re-read also ships and appends a full duplicate row-set to collect.query_store_stats (append-only COPY, no unique key by design; dedup is read-side ROW_NUMBER ... rn = 1). Pure cost both sides, zero information.

Also for the record: the shipped cadence default is already 5 minutes (CollectorScheduleDefaults), not 1 — the 60s figure in this issue is the sweep watchdog, which a 20–100s QS run inside a body still trips whenever QS comes due.

Fix lanes, cheapest first

A. Cadence: 15 min for query_store on the primaries box (config_collector_schedules, zero code). Closed intervals lose nothing — they're immutable and get collected once regardless. The only grain loss is intra-open-interval progress snapshots (the current hour's bar refines 4x/hour instead of 12x). Cuts the daily QS aggregation bill ~3x immediately. Reversible.

B. Open-interval skip cycles (the code fix this issue should land). Split the read: NEW closed intervals (id above the store's max closed id) collect every cycle — they're cheap and final; the OPEN interval's cumulative snapshot only every Nth cycle (or when its projected staleness exceeds a threshold). Steady-state cost becomes proportional to interval closures (~1/hour/db) plus one open-interval refresh per N cycles, instead of a full open-interval re-aggregation every cycle. The store already carries runtime_stats_interval_id (V41), the per-database watermark seam exists (GetLastCollectedTimeForDatabaseAsync shape — a MAX(runtime_stats_interval_id) twin needs no migration, the #2150 query_id argument verbatim), and rn = 1 read-side semantics are unchanged. Test surface is well-pinned (QueryStoreCollectorDefinitionTests — the @cutoff_time/Assert.Single(plan.Parameters)/byte-identical-remainder pins all move deliberately).

C. Slice-grain redesign (ship immutable slices with a slice watermark, aggregate store-side): the theoretical minimum read, but it changes payload semantics, store shape, and every read — not proportionate while B exists.

Recommendation: A now for relief (operator call, one store row per box), B as the fix that closes this issue, keeping multi-53's ~50s/run as the before/after yardstick.

[erikdarlingdata]

Fix merged in 1e92720 (PR #2314).

Mechanism: most cycles now ship only intervals that have CLOSED (i.end_time <= SYSUTCDATETIME(), server-evaluated, single-parameter contract preserved) — they're immutable, so first collection is final. The OPEN interval is re-read only when its per-database stamp (qsowm:, owner query_store_open_interval) is at least 15 minutes old. Time-based rather than cycle-counting, so it survives cadence drift; include is the conservative default (absent/malformed/negative/overflow/future stamp all read as include-now), and the stamp lands only after that database's read AND flush succeed, so a tolerated per-database fault re-includes next cycle instead of spending the window.

Expected effect: at the 5-minute cadence that's ~4 open-interval reads per 60-minute Query Store interval instead of ~12 — roughly two thirds of the open-interval spend removed. Yesterday's hour on the use1 primaries: 219 runs averaging 35.5s (median 28.6s, max 447s; ayr-01 at 219s avg) — that's the baseline for the before/after.

Next: nightly + install on both boxes, then the production yardstick before closing.

[erikdarlingdata]

Yardstick result: the fix works exactly as designed, and the design was aimed at ~10% of the bill

Eleven hours of steady state on the new nightly (all 42 primaries, stamps healthy: 114 qsowm: rows, every one refreshed inside its 15-minute horizon).

What moved — the payload, dramatically:

hour (UTC)	avg rows/run	total rows/hr
20:00 (pre-install)	7,390	1,647,980
02:00	3,539	757,373
08:00	1,687	328,875

A 5x cut in shipped rows and the store-side write pressure that goes with it. The closed-only cycles ship near-zero, exactly per the mechanism.

What didn't move — wall clock: last-hour avg 37.5s / median 30.1s / max 360s, statistically identical to the 35.5s / 28.6s / 447s baseline.

Why, from the per-database splits:

multi-49  08:31  query_store => 0 rows     (sql:68,215ms)                        <- closed-only: full cost, nothing shipped
multi-49  08:44  query_store => 7,558 rows (sql:75,352ms)                        <- include-open: +7s for the whole payload
ayr-01    08:28  query_store => 0 rows     (sql:298,378ms)                       <- five minutes to produce NOTHING
ayr-01    08:43  [AYR] => 1,669 rows (sql:359,253ms = wm:5,245 + open:1,139 + drain:352,869)

The per-cycle cost is FIXED per database and invariant to what ships. Per-row drain on one server, one cycle: florafarms 26ms/row, canada 1.3ms/row — drain tracks the database's retained Query Store size, not the payload. The prime suspect: the runtime-stats read can't seek on an interval window (sys.plan_persist_runtime_stats keys on plan_id first), so every cycle scans the database's whole retained QS history and our predicates only trim what comes OUT. Secondary fixed cost: the per-database store watermark read (wm:) runs 0.5–5s per database — ~25 databases on a multi-tenant primary is up to a half-minute of our own store reads per cycle.

What this issue's fix still bought: 5x fewer rows written, read-side dedup pressure down, and the open-interval correctness contract pinned. Worth keeping on its own merits.

Options for the remaining 90%:

1. Cadence override to 15 min for query_store on use1 (config_collector_schedules) — if per-cycle cost is fixed, 3x fewer cycles is a 3x load cut for zero code. Reversible in one UPDATE.
2. Plan-shape fix: capture the actual plan on a heavy database (AYR) and, if it's the full-history scan, restructure the read to materialize qualifying interval_ids and drive seeks — a collector-query change with a measurable before/after.
3. Batch the per-database watermark reads into one store round-trip per server (the wm: slice).

ayr-01 also deserves its own look — 300+s for one database's Query Store read says its retained QS is enormous; a sys.database_query_store_options check (size, mode, cleanup settings) may show a store at its cap in READ_ONLY thrash.

Recommendation: 1 now (it compounds with everything), 2 as the real fix, 3 opportunistic.

[erikdarlingdata]

Refinement: the invariant cost hides in the drain split's fine print — and the prime suspect moved

Three read-only probes on ayr-01 narrow the mechanism past this morning's comment:

1. Our own query is invisible to the plan-cache read, by our own house style. The collector queries end OPTION(RECOMPILE), so they never persist in sys.dm_exec_query_stats — ayr-01's top-CPU list (6h) contains only app queries. That kills one measurement avenue but also bounds the shape: if the 300s cycles were raw CPU burn, ~21,600 wall-seconds of it in that window would distort everything else on an 8-core box, and the box sat at single-digit CPU.

2. The wait profile rules out the other two easy stories. Top waits, 6h: SOS_SCHEDULER_YIELD 567s (100% signal), WRITELOG 119s, PREEMPTIVE_OS_QUERYREGISTRY 107s, ASYNC_NETWORK_IO 75s, PAGELATCH_SH 64s. No QDS_* anywhere near the top, no PAGEIOLATCH — the QS read is not stuck on Query Store's internal locks and not reading a giant store off disk.

3. The drain: number is not what it looks like. The per-item sql: slice wraps the whole readItem delegate — which since #2210/#2150 includes the SEPARATE plan-XML fetch and statement-text fetch after the payload drain. On a closed-only cycle the payload is zero rows, so ayr-01's 0 rows (sql:298,378ms) can only be spent in those two fetch queries — both of which order over the database's Query Store catalogs (sys.query_store_plan / sys.query_store_query_text) to ship a watermark-bounded suffix. On a huge QS catalog, producing that ordered suffix is the invariant scan, and it runs every cycle regardless of the payload's interval logic — which is exactly why #2314 cut rows 5x and moved wall-clock 0%.

Next discriminator, cheap: split the fetch phases into their own log timings (plan_fetch: / text_fetch: alongside wm/open/drain) — a one-nightly instrumentation change that turns the next ayr-01 cycle into a definitive answer, and permanently upgrades the #2164 split for every future investigation. If the fetches own the bill, the fix is fetch-query shape (seekable watermark predicates on the QS catalogs, or skip-when-payload-empty: a cycle that shipped no runtime rows cannot have new plans or text to fetch — that last one might be the whole fix in one guard).

The skip-when-empty observation deserves emphasis: plan-XML and text fetches exist to resolve rows the payload just shipped. A closed-only cycle that ships nothing has nothing to resolve. Gating both fetches on batch.Count > 0 (or on the payload having shipped new plan/query ids) would make the closed-only cycles genuinely cheap and could deliver the original two-thirds win after all — pending the instrumentation confirming the fetches own the time.

[erikdarlingdata]

Instrumented readout (#2321, one cycle after install): the fetches own the bill, and the cost is scan-shaped

The split, ayr-01:

before:  [AYR] => 1669 rows (sql:359,253ms = wm:5,245 + open:1,139 + drain:352,869)
after:   [AYR] => 1,845 rows (sql:25,817ms = wm:3,285 + open:1,144 + drain:78 + plan_fetch:17,258 + text_fetch:4,052)
         [AYR] => 1,187 rows (sql:26,517ms = wm:2,204 + open:1,584 + drain:55 + plan_fetch:18,139 + text_fetch:4,535)

Fleet-wide, actual drain is now 20–100ms everywhere — the old blended drain was the two fetch queries, full stop. plan_fetch is the dominant term (florafarms 11.9s, willow 12.3s, insa 8.3s, COPPERSTATE 8.4s, columbiacare DBs 6–9.5s), with text_fetch a steady 1–4.5s.

Three sharper facts:

1. The cost is scan-shaped, not ship-shaped. Same server, same sweep: pecos plan_fetch:32ms, canada 26ms, spruce 45ms — against florafarms 11,927ms. A fetch that finds nothing CAN be cheap; on a database with a big Query Store catalog it isn't, because the candidate selection pays before knowing there's nothing to ship.
2. Closed-only cycles still pay it. ayr-01's 0-row cycles on the new binary: 23.2s and 22.5s. So skip-when-payload-empty (my earlier sketch) is NOT the fix — it would help 2-of-3 cycles on the heavy databases but the include-open cycles keep paying ~20s of scan, and the wm+open floor stays.
3. ayr-01's own morning corroborates the catalog-size correlation from the outside: its per-cycle cost collapsed 298s → 29s at ~09:30, BEFORE the 10:24 install — the timing signature of Query Store size-based cleanup shrinking the catalog. Nothing we shipped did that.

Fix target, now precise: the candidate-selection shape of the #2210 plan fetch (and #2150 text fetch to a lesser degree) — make "nothing new" cheap on a big catalog. Reading the fetch SQL next to see whether that's a missing seek on the watermark bound, an aggregate over the whole catalog, or a join order the optimizer can't save; will post the proposed query change here before building it.

[erikdarlingdata]

Diagnosis complete — two findings in the plan fetch, one small and one structural

Read the fetch SQL and its sizing after the instrumented readout. The query itself is seek-shaped (plan_id > watermark ORDER BY plan_id TOP(K)); the cost is in what it fetches and how much it decompresses to decide.

Finding 1 (small, a dropped wire): the adaptive candidate sizing is built but never connected

QueryStorePlanXmlState carries the whole adaptive design — ObservedAvgPlanBytes (learn each database's real average from its own shipped passes), the catch-up floor overload with its measured 3x-underestimate rationale, the 11x fleet spread argument for why no constant works. The single call site passes observedAvgPlanBytes: null (DarlingCollectorRunner.cs:1382), so every pass on every database sizes K from the 160KB first-contact seed: K ≈ 118, always. The learning loop was designed, documented, and never plugged in. Small fix, real but secondary win (right-sizes decompression per database).

Finding 2 (structural, the actual bill): the fetch ships the WHOLE plan catalog, not the plans we collected

The walk is plan_id > watermark over sys.query_store_plan — every plan Query Store ever persisted, in id order, regardless of whether any collected runtime row references it. On this fleet that's expensive twice:

• Per cycle: a churn-heavy database (the literal-variant pattern Per-query CPU attribution unusable on a plan-churning instance: reads see 18 execs where Datadog sees 43% of the box; literal fragmentation defeats top-N #2235 documented on this very app) mints new plans continuously, so the backlog never drains, and every cycle decompresses ~K plans' XML to budget-cut a 12MB shipment of mostly junk-variant plans — the 6–18s plan_fetch: the instrumentation just isolated, paid whether or not the payload shipped anything.
• In the store: this is query_plan_dim is 63% of the Darling store and never shrinks — and query_store_stats compresses at 2.7x against siblings' 10-30x #2316's other shoe. query_plan_dim at 121 GB (63% of the store) is the accumulated XML of entire plan catalogs, including every literal-variant plan for queries whose runtime rows we never collected and that no read path will ever join to.

Proposed fix — activity-driven plan fetch: fetch XML only for plan_ids referenced by collected runtime rows (the payload already knows them; the read side only ever joins query_plan_dim from collected stats, so an unreferenced plan's XML is unreachable by construction). The plan_id-linear watermark becomes a seen-plans set question — the natural shape is 'plans in this cycle's payload not yet in the store', which also makes 'nothing new' genuinely free. Cuts the per-cycle fetch to plans that matter AND bends #2316's growth curve with the same change.

Sequencing I'd propose: wire Finding 1 now (one-line-ish, no semantics change), design-comment Finding 2 here before building (watermark semantics change + a decision about whether existing unreferenced XML in the store gets the #2316 orphan-prune treatment). The cadence-override question from this morning stays live as the zero-code bridge either way.

[erikdarlingdata]

Finding 2 design: activity-driven plan fetch (posting before building, per the sequencing)

The shape change in one sentence: stop walking sys.query_store_plan by plan_id > watermark, and instead fetch exactly the plans this cycle's collected runtime rows reference that the store does not already hold — which makes the store itself the watermark and deletes the concept of a plan-fetch backlog.

Mechanics

1. The payload already names its plans. Every shipped runtime row carries plan_id; the runner has the cycle's distinct set client-side for free.
2. Missing-set check against our own store, not the target. query_plan_dim is keyed by (server, database, plan_id); one indexed ANY($ids) read per database per cycle answers "which of these lack XML" in milliseconds — against OUR store, where reads are cheap and ours to spend. Optionally cached as a per-database HashSet seeded once per process lifetime (plan_ids are ~8 bytes; even a huge catalog is small memory), making steady state zero store reads too.
3. Fetch by ID list. The target-side query becomes WHERE plan_id IN (…) (chunked), ordered by plan_id, same byte budget. On a caught-up database the missing set is EMPTY and no target query runs at all — 'nothing new' costs literally nothing, which is the property the instrumented readout showed today's shape lacks (23s to find nothing on ayr-01).
4. The budget cut retries itself. Ids deferred by a budget cut remain missing, so they are automatically next cycle's candidates. No watermark state, no suffix-safety argument, no out-of-order hazard — the whole planwm: machinery (and its prune registration) retires. Plan XML is immutable per plan_id in Query Store, so once stored, never refetched.
5. Text fetch gets the identical treatment by query_id against query_store_text.

What changes in behavior, honestly stated

• Plans never referenced by a collected row never get XML. That is the point, not a cost: the read side only ever reaches query_plan_dim by joining FROM collected stats, so an unreferenced plan's XML is unreachable today — we pay to ship and store it, and nothing can display it. On this fleet's literal-variant churn that is the majority of the catalog, which is also why this same change bends query_plan_dim is 63% of the Darling store and never shrinks — and query_store_stats compresses at 2.7x against siblings' 10-30x #2316's 121 GB curve going forward.
• A plan referenced only by rows below the payload's TOP(50000)/noise floors stays XML-less until it matters enough to ship a row. Consistent with the product's own thresholds.
• Transition: existing stored XML stays; the retired planwm:/text-watermark state rows get pruned once (the existing prune machinery's job); already-stored unreferenced XML is query_plan_dim is 63% of the Darling store and never shrinks — and query_store_stats compresses at 2.7x against siblings' 10-30x #2316's separate orphan-prune decision, unchanged by this.

Cost/risk

• Target-side: replaces the per-cycle catalog walk + K-plan decompression with either nothing (steady state) or an ID-list fetch of plans we will actually store. The IN-list chunking is the only new query shape; ORDER BY plan_id keeps the budget cut deterministic.
• Store-side: one ANY($ids) probe per database per cycle (or amortized to ~zero with the HashSet).
• The Wire the plan fetch's adaptive candidate sizing (#2312 Finding 1) #2322 sizing wire stays useful for the transition period and for genuinely-new-plan bursts (the budget still bounds a cycle that references hundreds of new plans).

⚖ Your call before I build: (1) green-light the shape? (2) with or without the in-memory known-ids cache in v1 (I'd start WITHOUT — the ANY() probe is already cheap and simpler to reason about)? (3) should the retired watermark state prune in the same PR or a follow-up?