[PERF] QS collector drain-mode passes cost the full byte budget every cycle — make the budget a knob, skip already-shipped plans by hash

[erikdarlingdata]

Component

Shared collectors (QueryStoreCollector), both apps

Field evidence (dogfood, 2026-08-10)

A 4-core multi-tenant box post-resize-restart (all plans recompiled) + a freshly restored tenant catalog: every database hit MaxTextBytesPerDatabase (64MB, compile-time const) on every pass, at 44–109s of SQL time per database, ~380–427s per full server pass, repeating every cycle. The staged shape (#2134) worked exactly as designed — bounded, resumable, no wedge — but the drain tax is large and highly visible (60–100s PerformanceMonitorLite statements in sp_WhoIsActive on a prod primary).

Why passes cost this much

1. The per-pass ceiling is generous and fixed: MaxTextBytesPerDatabase = 64 * 1024 * 1024 (QueryStoreCollector.cs:327). 64MB of QS text/plan extraction ≈ 45–110s on small hardware because CONVERT(nvarchar(max), qsp.query_plan) decompresses plan XML server-side — that's the dominant CPU term.
2. No cross-pass plan memory: query_plan_text ships once per plan per pass (ROW_NUMBER() OVER (PARTITION BY qsp.plan_id ...) = 1). A hot plan re-decompresses and re-ships every cycle; the store dedups on arrival — after the server already paid the extraction. In a recompile storm, most of every 64MB is plans the store has.

Proposed levers

• (a) Budget knob: make the per-database text byte budget a store setting (per-server override ideal), default 64MB, floor ~4MB — the Expose the hardcoded self-alert thresholds as store-backed settings #2107 store-backed knob pattern. Small boxes drain in 15–25s bites instead of 60–110s ones; same total work.
• (b) Known-hash skip (the big win): qsp.query_plan_hash is available before the CONVERT. Maintain the recently-shipped hash set per (server, database) in collector state, pass it in (temp table / TVP), and extend the ship condition to ROW_NUMBER = 1 AND qsp.query_plan_hash NOT IN (SELECT h FROM #known). Recompile storms then ship stats rows but almost no plan payload. Caveat to design around: query_plan_hash is a plan-shape hash, not a full-XML digest — distinct XML can rarely share a hash; acceptable for perf telemetry but should be a documented tradeoff (or refresh hashes older than N days).

The two compose: (b) shrinks steady-state and storm payloads; (a) bounds the worst case on hardware that can't afford big bites.

[erikdarlingdata]

Measured result from the dogfood fleet, and it falsifies this issue's premise. Reading it before building the second half.

Cut the per-database text budget 64 MB → 12 MB (5.3x less payload) on a cross-region 4-core primary and measured the same server's full query_store pass, same five databases:

	rows shipped	sql: time
64 MB (10 passes, 19:51–22:15)	2,530–8,626	358–480 s
12 MB (2 passes, 22:35–22:49)	698, 1,233	383 s, 352 s

Per-database on the biggest tenant: 102/110/107/109 s at 64 MB → 90/80 s at 12 MB.

So ~7x fewer rows and 5.3x less text moved buys ~0–20% of the time. Payload size is not what dominates these statements, which means both halves of my plan were aimed at the wrong term:

• The byte budget stops the CLIENT reading partway through the second statement. It cannot touch the first statement — the Query Store: the in-memory and flushed slices of one runtime_stats_id collide in the read-side dedup, understating execution counts non-deterministically #1907/Query Store payload stages its aggregate — the fixed cost big catalogs couldn't pay #2134 staged aggregate that materializes #pm_qs_slice from sys.query_store_runtime_stats over the interval range. That aggregate completes in full before a single row ships, so no client-side cut can shorten it.
• The known-hash plan skip I was about to build targets CONVERT(nvarchar(max), qsp.query_plan) in that same second statement. If shipping isn't the cost, skipping plan XML isn't the win either. Not building it on this premise.

Caveat on the evidence: two post-change passes, and evening traffic was declining (which would if anything flatter the new numbers). I'll keep sampling. But 383/352 s sits squarely inside the pre-change 358–480 s band while payload fell 5x — that is enough to redirect.

Where the cost actually has to be: the slice aggregate's scan of the runtime-stats catalog. Which means the real levers are (a) bounding the aggregate's input — push the row/interval limit INTO the first statement instead of only capping the second, (b) narrowing the interval range harder per pass (a behind watermark plus 1-minute intervals means the aggregate spans a lot of catalog for every pass), or (c) reducing how often the full aggregate runs at all on servers where it is this expensive.

Next step is measurement, not code: I need the per-STATEMENT split (aggregate vs final select) rather than the batch total the log reports, since ExecuteReaderAsync blocks through the non-rowset INSERT and the two are currently indistinguishable in sql:. Adding that instrumentation is cheap and makes the next fix falsifiable instead of another plausible guess.

The V59 knob stays — it is still correct as a memory bound and it did cut bytes moved 5x, which matters for the client and the wire even though it barely moved the server-side clock. It just isn't the drain fix, and this issue's title overstates what a budget can do.

[erikdarlingdata]

The split is deployed and it falsifies my prediction — and my earlier conclusion. Reporting both.

I said to expect open ≥90%. First seven samples, three servers, all at the 12MB budget:

server	database	rows	sql	wm	open	drain	open%
multi32	Redstone	496	25,910	265	913	24,732	3.5%
multi-03	bloomwellness	201	3,863	—	167	3,553	4.3%
multi-03	cashew	286	3,959	—	159	3,596	4.0%
multi-03	qualla	296	12,034	—	371	11,372	3.1%
multi32	Surge	107	35,650	—	1,027	34,303	2.9%
multi-03	walnut	583	6,968	—	232	6,570	3.3%
columbia	ash	68	51,376	—	3,229	48,014	6.3%

Drain is 94–97% of every pass. Open is 3–6%. So the aggregate is NOT the dominant term — I was wrong about that, and the instrumentation is exactly what was needed to find out rather than continuing to reason from the budget experiment.

But drain is not bandwidth either, and that's the interesting part. Look at the row counts against the times: 68 rows took 48 seconds of drain. 107 rows took 34 seconds. That is 450–750 ms per row. Nothing about moving 12MB explains a half-second per row; that is round-trip latency, not throughput.

Which reconciles the earlier "5x less payload, no change" result. Both experiments are consistent with: drain cost is dominated by per-row LOB round trips over a high-latency link, where each nvarchar(max) (query text, plan XML) is streamed in chunks and every chunk is a network round trip. Cross-region RTT × thousands of chunks is the pass time. Bytes matter only insofar as they set the chunk count, and a 5x byte cut spread across the same rows leaves the per-row round-trip floor intact.

This puts the known-hash plan skip back on the table, for a different reason than I originally had. I killed it because it targets plan-XML bytes and bytes looked irrelevant. But if the cost is per-row LOB transfer, then not fetching plan XML for plans the store already holds removes those round trips entirely rather than shrinking them — a far better lever than trimming a budget. I was right to stop building it on the old rationale and wrong to conclude it was dead.

Cheaper things to test first, in order:

1. Connection packet size. Nothing in the codebase sets Packet Size; the default 8KB means a 176KB average plan is ~22 round trips. Raising it toward 32KB is a connection-string change with no code risk, and it's the classic fix for exactly this shape.
2. CommandBehavior.SequentialAccess. Not used anywhere — the readers open with the default, which buffers the whole row. For rows carrying multiple MAX columns, sequential access plus a large read buffer changes the streaming pattern materially.
3. Then the hash skip, measured against 1 and 2 rather than assumed.

Next step is testing (1) on the dogfood fleet, since it's reversible and free. I'll report the split before and after rather than predicting this time.

[erikdarlingdata]

The 32KB packet size is reverted (#2179). Reporting the failure honestly, because the failure is mine, not the hypothesis's.

I merged and deployed a connection-level change having never opened a connection with it. My test asserted the connection string contained PacketSize=32768 and that the value was inside the driver's accepted range. Both passed. Neither touches the only risk that matters: a packet size either negotiates or the transport breaks — there is no "slightly wrong."

Fleet result, with the prior deploy as a clean control:

box time	event	failures
08:38	open/drain deploy, no packet change	0
09:22	32KB service start	—
09:23:32 – 09:24:49	44 transport-level errors across 44 servers	77-second burst
after 09:24:49, still 32KB		506 successes, 0 failures

A transport-level error has occurred when receiving results from the server — once per server on first connect at the new size, then clean on retry. Self-healing, and therefore easy to have missed if I had only looked at a freshness count instead of the error log. On an operator's fleet it would have produced a burst on every service start.

What this does NOT invalidate: drain is still 94–97% of a query_store pass, and drain throughput still tracks LOB size per row rather than total bytes (256 KB/s at ~180 KB/row vs 1,080 KB/s at ~41 KB/row, same link, same code, same budget). Per-packet overhead remains the best explanation for that shape. The lever looks right; my delivery of it was not.

Constraints the next attempt inherits:

1. A live connect at the candidate size, against a representative instance, before merge. String assertions do not count.
2. Do not jump to the protocol maximum. Something in the path — RDS, the encryption layer, an MTU interaction — objects to 32768 on first negotiation. An intermediate value, or negotiate-upward-with-fallback, is the shape that can roll out safely.
3. Probably per-server rather than global, so one unhappy instance cannot produce a fleet-wide burst.

Next lever I'll try instead, since it needs no transport negotiation: CommandBehavior.SequentialAccess on the collector readers (unused today — every row is buffered whole, including both nvarchar(max) columns, before the loop sees it). Same measurement protocol, and this time the pre-merge test opens a reader.