[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

[erikdarlingdata]

Evidence (from the #2148 reporter's log — thank you, TrudAX)

Same two Azure SQL DB targets (elastic pool, edition 5, "version 12"), same day, before and after the 3.3.0 → 3.4.0 upgrade at 09:28–09:38 local:

build	runs	median	max
3.3.0 (00:00–09:28)	198	4.8 s	31 s
3.4.0 (09:38–12:00)	6	—	99.8 min

Post-upgrade runs: 0.1 min (7,748 rows), 37.6 min (9,480), 46.1 min (11,845), 82.1 min (5,951), 0.1 min (19,873), 99.8 min (6,621). Bimodal — occasionally the old speed, usually catastrophic, on BOTH servers, at tiny row counts. sql: time ≈ wall time, so it's server-side execution, not storage writes.

Because Lite's collectors run sequentially, one 100-minute query_store cycle starves every other collector — this is the actual mechanism behind #2148's "all collection stopped" (the abandonment containment in #2149 protects backfill and the connection check; the LIVE collector pipeline is still sequential and still waits).

Why the 30s CommandTimeout never fired: rows trickle continuously, and SqlClient's timeout resets on every network read — a slowly-streaming result set never times out. Any fix should consider a wall-clock bound as defense in depth.

Suspects

QueryStoreCollector.cs changed by ~958 lines between v3.3.0 and v3.4.0 (the #2134 staged-#temp rewrite with OPTION(RECOMPILE) both statements + interval pre-filter, plus #2058 backfill plumbing). The rewrite was validated hard on SQL Server (fixed the fleet's wedged-core catch-up), but Azure SQL DB elastic pools differ in exactly the ways that matter: resource-governed tempdb, DTU throttling, and a different optimizer surface for the query_store TVFs. The bimodality smells like a per-cycle plan flip on the staged aggregate despite the RECOMPILE hints — or the Azure arm taking a different, unvalidated path.

Next steps

1. Determine which arm (regular vs Azure) edition-5 targets execute in 3.4.0, and diff the emitted SQL against 3.3.0's.
2. Repro on an Azure SQL DB/elastic pool at low eDTU with a non-trivial Query Store; capture the actual plan for the staged INSERT…/SELECT INTO at both speeds.
3. Consider a wall-clock bound on the per-database item as containment regardless (the streaming-defeats-CommandTimeout gap).

Until fixed: 3.3.0 is the honest recommendation for Azure SQL DB users (told the reporter as much on #2148).

[erikdarlingdata]

SQL-shape diff confirms the mechanism

3.3.0 (4.8 s median on the reporter's Azure targets): the payload was one monolithic join — SELECT TOP(N) … FROM sys.query_store_runtime_stats JOIN plan/query/text … WHERE last_execution_time > @cutoff ORDER BY last_execution_time DESC OPTION(RECOMPILE, LOOP JOIN). Crucially, TOP(N) + that ORDER BY lets the engine short-circuit: on a small-to-huge Query Store alike, it reads newest-first and stops at N rows.

3.4.0 (37–100 min): the #2134 rewrite stages the FULL aggregate first — SELECT … INTO #pm_qs_slice FROM sys.query_store_runtime_stats WHERE interval_id IN (…) GROUP BY … HAVING … — and only applies TOP at the final join from the temp table. That was the deliberate, field-validated fix for the wedged-core pathology on big on-prem/RDS fleets (the monolith's LOOP JOIN re-materialized the QS TVFs per probe — fixed cost ~30s at ANY window width, catastrophic at fleet scale). But it means the aggregate ALWAYS pays for the whole eligible window before any row limit applies. The reporter's PBMS_PERF/PBMS_SIT are performance-test databases — exactly the profile with an enormous Query Store — and Azure SQL DB inherited the staged shape through the shared payload body (BuildQuery → AzureEligibilityGateText + BuildPayloadBody).

So each shape is right where it was validated and wrong where it wasn't:

shape	on-prem/RDS fleet	Azure SQL DB
3.3.0 monolith + LOOP JOIN	30s+ fixed cost, wedged cores (#2133)	4.8 s median, months of history
3.4.0 staged #temp	6 s, fleet converged in 1 h	37–100 min

Proposed fix: shape by target class

BuildPayloadBody branches on IsAzureSqlDb: Azure targets get the 3.3.0-proven monolithic shape back (verbatim — including its LOOP JOIN, which was never a problem on single-database-scoped Azure Query Stores); on-prem/MI keep the staged shape (#2134's win stands untouched where it was earned). This is empirically justified in both directions — neither shape ships anywhere it wasn't field-validated — and does not depend on fully explaining the bimodality (which still wants an actual Azure plan; the interval pre-filter's interaction with wide post-stall windows is the leading theory for why occasional runs are fast).

Backfill's Azure arm needs the same branch check (it shares payload plumbing via BuildBackfillQuery).

[erikdarlingdata]

Update: bimodality points at plan variance, and a one-paste diagnostic will settle it

Two refinements from deeper log analysis before changing any code:

1. A naive revert to the 3.3.0 shape is off the table — between the releases sits a correctness fix (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 returns an interval's flushed and in-memory slices as separate ADDITIVE rows; the collector now sums them at the source). Reverting Azure to the raw-row monolith would quietly reintroduce that undercount. Any Azure-specific shape must keep the slice aggregation.
2. The slow/fast pattern is not window width: the reporter's FASTEST post-upgrade run shipped the MOST rows (19,873 in ~6s) while 9–12k-row runs took 37–100 minutes. Both statements run under OPTION(RECOMPILE), so each cycle replans — this smells like the recompile flipping between a good plan and a catastrophic one (per-probe TVF re-materialization by another name) on Azure's optimizer surface.

@TrudAX — your own Query Store has already captured everything we need: the monitor's statements, their per-plan durations, and the plans themselves. If you can run this one query in the affected database (PBMS_PERF's database is ideal) and attach the results — grid as text/CSV, and ideally save the query_plan XML of the slowest and fastest plan_ids as .sqlplan files:

SELECT
    qsq.query_id,
    qsp.plan_id,
    rs.count_executions,
    avg_minutes = CONVERT(decimal(10,2), rs.avg_duration / 60000000.),
    max_minutes = CONVERT(decimal(10,2), rs.max_duration / 60000000.),
    query_preview = LEFT(qst.query_sql_text, 120),
    qsp.query_plan
FROM sys.query_store_query_text AS qst
JOIN sys.query_store_query AS qsq
  ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_plan AS qsp
  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = qsp.plan_id
WHERE qst.query_sql_text LIKE N'%PerformanceMonitorLite%'
AND   qst.query_sql_text NOT LIKE N'%query_store_query_text%'
ORDER BY rs.avg_duration DESC;

That shows which of the collector's two statements is slow (the SELECT … INTO #pm_qs_slice stage or the final join) and — if the plan-flip theory is right — the same statement appearing under multiple plan_ids with wildly different durations. With those plans in hand the fix stops being a guess.

(Using your Query Store to debug the Query Store collector has a certain justice to it.)

[TrudAX]

can you read Excel it has 30 rows and 3 query plan on a separate page

queryPlans.xlsx

[erikdarlingdata]

Excellent data — and it eliminates my leading theory. Reading your export:

• Your top-30 by average duration is entirely the monitor's sessions query (~0.8–1.5 min avg — itself worth a look later, but not this bug).
• The 3.4.0 query_store statements (SELECT … INTO #pm_qs_slice / the final join) don't appear at all — so server-side, they're fast on your database.
• Yet the client measured 37–100 minutes in its SQL phase, which spans connect + execute + reading the rows. On your slowest run that's ~900 ms per row, thousands of times — and occasionally the same cycle runs at full speed instead.

So the time is being lost somewhere the server's Query Store can't see it (or in many small executions my first query sorted past). Two short asks, either one likely decisive:

1. Catch it in the act (the conclusive one). Next time the CPU chart stalls (any cycle after a Lite restart on 3.4.0 should reproduce within ~15 minutes), run this in the affected database and paste the result:

SELECT
    r.session_id, r.status, r.command, r.wait_type, r.wait_time,
    r.total_elapsed_time, r.cpu_time, r.logical_reads,
    query_preview = LEFT(t.text, 200)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE t.text LIKE N'%PerformanceMonitorLite%'
AND   r.session_id <> @@SPID;

If a monitor query shows up waiting on ASYNC_NETWORK_IO, the server is waiting on the app (client-side bug, my problem in the read loop). If it shows a resource/governance wait, it's pool throttling the server-side work. If nothing shows up at all while the app says it's mid-collection, the time is pure client-side (or connection churn) and the app never reaches the server — equally diagnostic.

2. Re-sort for many-small-executions. The first query sorted by average and would hide anything executed thousands of times. Same query as before, but ordered by total:

ORDER BY rs.avg_duration * rs.count_executions DESC;

(top 10 is plenty). Thanks for the fast turnaround — between these two we'll have it cornered.

[erikdarlingdata]

ROOT CAUSE FOUND — and the fix already exists

Correcting my own earlier analysis, which had the timeline backwards. Settled by git, not theory:

What 3.4.0 actually ships (v3.4.0 tag): the query_store payload is the #1907 slice-aggregating derived table joined monolithically into the query_store_plan/query/text TVFs with OPTION(RECOMPILE, LOOP JOIN). The #1907 aggregation removed the old TOP(N) short-circuit (correct — the slices must be summed), which turned the long-standing LOOP JOIN hint from harmless into catastrophic: nested-loops from an aggregate with fixed-guess cardinality into the QS TVFs re-materializes a TVF per probe. That's a fixed ~30s+ cost at ANY window width on a big Query Store — 37–100 minutes on @TrudAX's perf-test databases. The bimodality is RECOMPILE occasionally landing a tolerable plan despite the hint.

This exact pathology was independently diagnosed on our own 54-server production fleet (#2133, field bisection on an 82k-plan catalog: the aggregate alone ran 81 ms, each TVF bare-scanned in ~300 ms, but aggregate-JOIN-TVFs could not finish in 30 s hinted or not; staged through a temp table the same work totaled 524 ms) and fixed by #2134 — staging the aggregate into #pm_qs_slice so the final join gets real row counts, LOOP JOIN hint removed permanently. That fix merged to dev on Aug 9, ~38 hours after the v3.4.0 tag was cut. It has been running in production against 54 servers since, where it converged a ~30-hour collective Query Store backlog in under an hour.

So: 3.4.0 shipped the bug; dev has carried the fix since the day after; no Azure-specific change is needed.

@TrudAX — reversing my earlier "stay on 3.3.0" advice: the current nightly build contains the #2134 fix (plus the collection-stall containment from #2149). If you're willing to try it, your query_store cycles should return to seconds. I'm also validating the fix independently on a purpose-built Azure SQL elastic pool today and will post numbers.

Remaining on this issue: post the Azure A/B validation numbers, then this closes via the 3.5.0 release that carries the fix to a stable tag.

[erikdarlingdata]

Azure validation rig results (Standard 50 eDTU elastic pool, one database, Query Store at capture-ALL with 1-minute intervals, ~5,000 distinct queries / 60k runtime-stats rows / 18 intervals):

shape	run 1	run 2
3.4.0 shipped payload (monolith + LOOP JOIN)	9.8 s	5.6 s
dev payload (#2134 staged)	7.2 s	6.2 s

Honest read: both shapes execute correctly on Azure SQL DB (that was worth proving — the staged temp-table shape had never run on Azure), but at this small catalog the pathology doesn't manifest — as expected, since the LOOP JOIN's per-probe TVF re-materialization cost scales with plan-catalog size (#2133's field case was an 82k-plan catalog; this rig has 5k trivial ones). Synthesizing a catalog of that size and text/plan weight would take hours and still be weaker evidence than the real thing.

So the validation that matters is @TrudAX's databases on the nightly — the root cause itself already stands on the production field bisection in #2133 (same shape, same wedge, measured). Rig is torn down; costs stop.

[erikdarlingdata]

Status update while validation is pending: dev has continued to move under this — alongside the staged-slice fix (#2134) it now carries the hang-containment primitive from the stall investigation (#2149), the retention deadlock retry (#2145), the restamped 3.4.0 plain archives (the version-metadata half of the field reports), and an MCP SDK bump (#2100). The ask from the previous comment stands: a run on nightly 3.4.0-nightly.20260809 or later on the affected elastic-pool environment is the confirmation gate — 3.5.0 is queued to bundle all of the above once that's green.