Query statistics misread a cached plan's statistics restart: counters that re-grew are taken as increments (row-coherent reset needed) #4428

Claude posting for Erik Darling

Problem

When SQL Server resets a cached plan's statistics and the plan comes back under the same key, the query-stats collector misreads the restart. Counters that re-grew past their old values are taken as ordinary increments, and counters that didn't re-grow are taken as reset. So one row gets a mix of under-counted deltas and a "no delta knowable" CPU with a zero interval.

• Where: PerformanceMonitor.Collectors/QueryStatsCollector.cs (shared by Darling and Lite). It makes one CalculateDeltaWithSeriesAge call per counter family, each with its own cache, for the same key (sql_handle:start:end:plan_handle). CollectorDeltaCalculator.Core decides the reset path per family.
• Field evidence (read-only, 24 h, two production SQL Server stores): every query_stats row with a zero interval but a nonzero execution or elapsed delta followed a DROP in total_worker_time for the same key. 188 of 188 on one store and 8,807 of 8,807 on the other; no first sightings, and no rows without a drop.
  • In most, the execution count had re-grown past its old value. For example, execution count 1 → 16 while total_worker_time went 57,695,259 → 703,943 µs. The collector stored an execution delta of 15 (the true work since the restart is 16), and CPU as unknown with a zero interval.
• Effect:
  • executions and duration are under-counted on those rows;
  • before Stop query_stats from recording a false zero CPU when only the CPU delta is unknowable (#4394) #4423, the zero interval also made the interval-honest rollups drop the rows entirely;
  • Stop query_stats from recording a false zero CPU when only the CPU delta is unknowable (#4394) #4423 keeps them, with CPU unknown, but with deltas taken across the restart.

Fix

• A row-coherent reset decision in CollectorDeltaCalculator: if ANY of a row's counter families would take the reset path, the whole row is a reset.
  • When the Per-query CPU attribution unusable on a plan-churning instance: reads see 18 execs where Datadog sees 43% of the box; literal fragmentation defeats top-N #2235 series-age test places the restart inside the gap, every counter's delta is its current value (the work since the restart), over the real interval.
  • Otherwise the whole row has no delta knowable, (0, 0).
  • Stop query_stats from recording a false zero CPU when only the CPU delta is unknowable (#4394) #4423's unknown-CPU handling stays as the fallback.
• Apply it to QueryStatsCollector first. wait_stats and PostgreSQL pg_statement_stats show related rows in the same census and follow once their shape is measured.

Related

#4394 (where this was found), #4423, #2235, #2234.