get_query_store_duration_trend ranks the raw Query Store slab per call and times out at any window width on a large store #2736

get_query_store_duration_trend cannot complete on a large store at any window width. On a store carrying ~6.9M query_store_stats rows per 6 hours, it timed out on a full day, on a 2-hour historical slice, and on a 2-hour window anchored at now — the last one as a control.

Observed

get_query_store_duration_trend times out at any width — full day, 2-hour slice, and 2-hour window anchored at now (that last one as a control). It's the instrument, not the data.

The control matters: a cost proportional to the requested window would have let the 2-hour-at-now probe through. It didn't, because the query's dominant cost is fixed, not window-proportional.

Mechanism

DarlingMcpTrendTools.GetQueryStoreDurationTrend (Darling/PerformanceMonitor.Darling.Service/Mcp/DarlingMcpTrendTools.cs:439-483) runs DarlingTrendReader.QueryStoreDurationTrendSql (Mcp/DarlingTrendReader.cs:407-472). Arm 1 — the one that matters on current data — is a ROW_NUMBER() dedup over the RAW hypertable, partitioned at full interval-identity grain (database_name, query_id, plan_id, runtime_stats_interval_id, first_execution_time, execution_type_desc, replica_role), windowed on interval_start_time_utc but chunk-excluded only by a deliberately slack collection_time slab (DarlingTrendReader.cs:436-437):

AND   collection_time >= $2 - interval '1 day'
AND   collection_time <= $3 + interval '30 days'

So even the 2-hour now-anchored control scans and RANKS at least 26 hours of raw rows for the server — tens of millions at this store's scale — before the aggregate sees a single row. The whole thing runs as the mcp role under its statement_timeout backstop (default 15s, DarlingManagedRoles.cs:411-412, #2357), which is the timeout actually observed. Raising the timeout is not the fix; the read is doing #1841's dedup the expensive way, per call, forever.

I checked whether this is the #2675/#2676 shape (a CTE re-evaluating an expensive expression N times): it is not — placed is referenced exactly once. This is the OTHER house enemy: a naive rank-then-aggregate over the raw Query Store slab at fleet scale, the class of cost that sp_QuickieStore-style materialization and the #1849 corrected rollups exist to avoid. And the rollups are sitting right there: query_store_stats_corrected_hourly materializes exactly this series' ingredients — execution_count_sum, duration_us_weighted_sum, deduped at interval grain (Darling/PerformanceMonitor.Darling.Storage/TimescaleSupport.cs:1335-1355) — and the composer routes to it (Compose/ComposeSourceRouter.cs:115). The trend tool never does; nor does its viewer twin (Darling/PerformanceMonitor.Darling.Viewer/ViewerDataService.QueryStore.cs, same SQL verbatim per the doc comment at DarlingTrendReader.cs:394-405), so fixing the reader fixes both apps if it lands at the shared shape.

Impact

This is the tool the MCP instructions crown as "the series that survives a failover and the one to reach for when a regression is older than the cache" — and on the box that holds all the live Query Store data, it is the one trend read that cannot run. The plan-cache trends answer instead, with exactly the eviction-blindness this tool exists to cover.

Fix direction

1. Route through the corrected CAGGs (preferred): serve the trend from query_store_stats_corrected_hourly (hour buckets are already this read's natural grain — points land at the hour the work ran), falling back to the raw arm only for the tail the rollup hasn't materialized, ComposeSourceRouter-style. Turns the read into a rollup scan; the Query Store hourly/daily CAGGs still bake in the pre-dedup inflation (#1841 tier 3) #1849 boundary disclosure applies as it does everywhere else.
2. Tighten the slab as a stopgap: the + interval '30 days' upper bound and - interval '1 day' lower bound are chunk-exclusion generosity; measuring what the closing-fetch distribution actually needs would shrink the ranked set by an order of magnitude, at some near-boundary accuracy cost that needs stating rather than guessing.
3. A dedicated tiny trend rollup (per server_id/hour, two sums) maintained by the service — smallest read, one more materialization to own.

Lineage, same dataset, same class of cost: #2675/#2676 (collector-side re-decompression), #2683/#2690 (plan-fetch bounding), #2700 (a heavy query_store run stalling the collection body). This is the read-side member of that family.