get_query_store_regressions sorts eight days of raw query_store_stats rows that the interval tables already dedupe #5809

Claude posting for Erik Darling.

Field (store A, the largest SQL Server fleet store; build 3.10.0-nightly.20261013.547; pm-worker, 2026-10-14)

14 days: 10 calls, 173 s, mean 17.3 s, p95 45 s, max 37.5 s. All 7 reads of 5 s or more since 10-05 asked for a 24 h window; the slowest statement is WITH deduped_baseline AS (, 37.4 s.

Code

Darling/PerformanceMonitor.Darling.Service/Mcp/DarlingQueryStoreRegressionReader.cs near line 94 reads raw collect.query_store_stats twice, the baseline [window start minus 7 days, window start) and the recent window, each with ROW_NUMBER() OVER (PARTITION BY seven keys ORDER BY collection_time DESC, execution_count DESC) to keep the latest snapshot per interval, then groups by database and query. The viewer's twin (Darling/PerformanceMonitor.Darling.Viewer/ViewerDataService.QueryStoreRegressions.cs near line 115) has the same shape. The read uses no interval table, rollup or daily summary, while get_query_store_top (#5168) and the history read (#5523, #5533) moved to query_store_interval_wide and query_store_interval_latest, which already hold the latest snapshot per interval and are bounded on first_execution_time both sides.

Hypothesis (inferred)

A window sort over eight days of raw rows, twice per call; time tracks raw row volume. The dedupe is work the interval tables already did at write time.

Fix

• Baseline and recent window read from the interval tables (interval_latest for the latest snapshot per interval), bounded on first_execution_time both sides like Query Store wide-table reads bound first_execution_time at the window end too (#5523) #5533, with the Query Store reads on the interval tables can miss an interval that started just below the table's floor #5541 floor rule: a store whose interval tables do not yet cover the window falls back to the raw read.
• The viewer calls the shared reader rather than keeping a copy, if the viewer's data service can take it; otherwise its copy takes the same shape in the same PR.
• A live test plants rows that regress between baseline and window and shows the same regressions from the new read as from the old.

Done when

The live test passes on both paths (interval and raw fallback), and pm-worker's recheck on store A shows a 24 h call under 5 s at p95.