Query Store: the in-memory and flushed slices of one runtime_stats_id collide in the read-side dedup, understating execution counts non-deterministically

[erikdarlingdata]

Found during the #1887 end-to-end smoke (live Azure SQL Database, GP_S_Gen5_1, shipped Darling service
from origin/dev @ 4adf3236). Not caused by #1872 — see "Why this is not the replica flip" below.
The #1887 run's own checks all passed; this is a separate, pre-existing defect that only a sustained
end-to-end run surfaces, which is exactly why the hand-composed SQL in #1848/#1872 never saw it.

Symptom

query_store_stats execution counts are understated, non-deterministically, on both the viewer grid
read (ViewerDataService.QueryStoreTopSql) and the query_store_stats_interval_hourly CAGG that the
corrected/daily rollups are built on.

Live example — the CAGG's last(execution_count, collection_time) versus the truth, same store, same run:

          module_name          | query_id | plan_id | cagg_execution_count | true_total
-------------------------------+----------+---------+----------------------+-----------
 dbo.pm_get_orders_by_customer |       12 |       2 |                  136 |        144
 dbo.pm_order_summary          |       14 |       3 |                    8 |         94   <-- 91% low
 dbo.pm_recent_orders          |       15 |       4 |                   61 |         69
 Adhoc                         |       38 |       6 |                    2 |          6

Two of the five rows report a number that is not merely stale but wildly wrong (8 instead of 94). Which
rows are wrong changes from run to run — it is a tie-break, not a consistent bias.

Root cause

sys.query_store_runtime_stats returns the flushed slice and the still-in-memory slice of the same
runtime_stats_id as two separate rows. Live from the Azure database (flush_interval_seconds = 900):

runtime_stats_id|plan_id|count_executions|first_execution_time       |last_execution_time
12              |2      |136             |2026-07-31 04:20:35.767    |2026-07-31 04:32:18.570   <- flushed, static
12              |2      |17              |2026-07-31 04:20:35.767    |2026-07-31 04:37:59.093   <- in memory, growing
14              |3      |86              |2026-07-31 04:20:35.837    |2026-07-31 04:32:18.580
14              |3      |17              |2026-07-31 04:20:35.837    |2026-07-31 04:37:59.100
15              |4      |61              |2026-07-31 04:20:35.853    |2026-07-31 04:32:18.603
15              |4      |17              |2026-07-31 04:20:35.853    |2026-07-31 04:37:59.117

total_rows|distinct_ids
12        |8

The collector's BuildPayloadBody selects straight from that view with no aggregation, so both slices
are stored. They then share every column of the read-side dedup key —
(database_name, query_id, plan_id, runtime_stats_interval_id, first_execution_time, execution_type_desc, replica_role)
— and the same collection_time, proven in the store:

 query_id | plan_id | runtime_stats_interval_id |  first_execution_time   | execution_type_desc | replica_role | rows_sharing_the_entire_dedup_key | execution_counts
----------+---------+---------------------------+-------------------------+---------------------+--------------+-----------------------------------+------------------
       12 |       2 |                         1 | 2026-07-31 04:20:35.767 | Regular             | Primary      |                                 2 | {8,136}
       14 |       3 |                         1 | 2026-07-31 04:20:35.837 | Regular             | Primary      |                                 2 | {8,86}
       15 |       4 |                         1 | 2026-07-31 04:20:35.853 | Regular             | Primary      |                                 2 | {8,61}
       38 |       6 |                         1 | 2026-07-31 04:29:04.077 | Regular             | Primary      |                                 2 | {2,4}

So both the viewer's ROW_NUMBER() OVER (PARTITION BY ... ORDER BY collection_time DESC) and the CAGG's
last(execution_count, collection_time) are ordering by a value that is identical for both rows. The
survivor is whichever the engine happens to emit first. When the in-memory sliver wins, the grid shows it.

The dedup itself is correct and load-bearing (#1841) — it is there to collapse re-collections of the same
interval across cycles. The bug is that it now also silently collapses two additive slices of one
interval within a single cycle, which is a different thing entirely.

The slices are additive — confirmed three independent ways

1. Arithmetic against the known workload. The seed ran each procedure a known number of times
   (100/50/25 in the burst, then one of each per 20s round; ~43 rounds by the last cycle → ~143/93/68).
   The sums land on 144/94/69. max() (136/86/61) does not.

2. The product's own procedure_stats collector, reading the entirely separate
   sys.dm_exec_procedure_stats, in the same store on the same collection cycle (04:35:03, against
   the query_store cycle at 04:35:03.870265 whose slices are tabulated above):

      collection_time       |        object_name        | execution_count
----------------------------+---------------------------+-----------------
 2026-07-31 04:35:03.753426 | pm_get_orders_by_customer |             144
 2026-07-31 04:35:03.753426 | pm_order_summary          |              94
 2026-07-31 04:35:03.753426 | pm_recent_orders          |              69

Exactly the slice sums ({8,136} → 144, {8,86} → 94, {8,61} → 69), and not max() (136/86/61).
The alignment holds at the next observation too: at 04:37:59 the DMV slices were 136+17, 86+17,
61+17 and procedure_stats at 04:38:05 recorded 153 / 103 / 78. This is in-product corroboration
that the correct total is SUM, not last/max.

3. The flushed row never absorbs the in-memory one. Across observations at 04:35 and 04:38 the 136
   row stayed at 136 with a static last_execution_time, while its twin grew 8 → 11 → 17 with an advancing
   one. A superseding row would have replaced it; an additive slice behaves exactly like this.

Why this is not the replica flip (#1872)

• sys.query_store_replicas holds one row per replica_group_id (4 groups, 4 names), so the
  LEFT JOIN ... ON qsr.replica_group_id = qsrs.replica_group_id the flip added cannot fan out:

replica_group_id|name_rows
1|1
2|1
3|1
4|1

• Both colliding rows carry the same replica_role (Primary), so the collision is identical to what
  it would be with the pre-flip NULL placeholder. replica_role is in the dedup partition either way.
• The duplication is visible in the raw DMV before any join (12 rows / 8 distinct ids, above).

Scope

• Mechanism is Query Store's flush behavior, not an Azure peculiarity — but live evidence here is Azure
  SQL Database only. Whether box SQL Server surfaces the same same-cycle duplicate pair is untested and
  should be checked before assuming either way.
• Affects query_store_stats_interval_hourly and everything derived from it
  (query_store_stats_corrected_hourly, the daily tiers) plus the viewer's Query Store grid. Related to but
  distinct from Query Store corrected rollups: the DAILY tier still carries a ~2x hour-straddle residual — an interval-grain daily re-dedup would remove it, at a fourth near-raw-cardinality CAGG #1869, which is about an hour-straddle residual in the daily tier, not a same-cycle tie.
• Lite reads the same shape (GetQueryStoreTopQueriesAsync) against DuckDB and looks to have the same tie —
  not verified live, flagged for the parity check rather than asserted.

Fix direction (not applied — this is a design call)

Aggregating the slices belongs on the collect side, where the identity is unambiguous: group the
payload by the natural runtime-stats key and SUM the counters (and take MAX(last_execution_time)),
so one interval yields one row per cycle and the existing cross-cycle dedup keeps working unchanged.
Doing it on the read side instead would mean teaching every consumer the difference between "same interval,
later cycle" (keep latest) and "same interval, same cycle" (add) — the CAGG's last() cannot express that.

I did not implement it: it changes the collector's emitted row shape and the semantics the #1841 dedup was
built around, which is a decision worth making deliberately rather than inside a validation run.

Found by: #1887 live Azure end-to-end smoke. Azure resources torn down.

🤖 Generated with Claude Code

[erikdarlingdata]

Shipped in #1919. The flushed and still-in-memory slices of one runtime_stats_interval_id are ADDITIVE members of one interval, and both were stored sharing the entire read-side dedup key plus collection_time - so every reader and rollup picked an arbitrary one (live: 8 executions reported where 94 was true). Not an Azure peculiarity: reproduced on box SQL Server 2022 with a forced flush, cross-checked against dm_exec_procedure_stats (125 and 70, exactly). The fix combines at collection in BuildPayloadBody (the #1844 single-body seam): SUM counts, count-WEIGHTED means (avg * count recovers each slice's total - Query Store never stores one), MIN/MAX extremes, and the cutoff moved from WHERE to HAVING so a static flushed slice cannot age out of the window and rebirth the defect as a sliver-only sum. Same defect, same fix in the deprecated Dashboard's proc (body-only change, no upgrade step). Measurably FASTER than what it replaces (fewer rows, half the nvarchar(max) shipped). Storage shape untouched - no migration, no version bump. Legacy pre-fix rows are made deterministic (19 read-side tie-break sites across both apps prefer the flushed slice) but remain under-counted; that residue and the un-tie-breakable CAGG buckets are #1912. Lite's tie-break guard was widened to an enumerated 3-file/7-site mirror of Darling's during review, with a cross-app pin on Darling's declared 12 so neither app can quietly fall behind the other.