WPF FinOps Server Inventory re-runs a 7-day raw query_stats DISTINCT for every server on the 30 s timer (~43 × 44 k buffers per refresh); query_stats_db_hourly answers it in 32 ms / 175 buffers

Problem

FinOps → Server Inventory loads the fleet list (GetServerInventoryAsync, cheap). It then calls GetServerMetricsAsync once per server, in pool-wide lanes, to overlay CPU, storage, idle-database count and provisioning status. The FinOps tab is an aggregate tab, so the fleet timer re-runs all of it every NocRefreshIntervalSeconds (default 30 s) while the sub-tab is visible.

ServerMetricsSql bundles five CTEs per server. The expensive one is idle_dbs:

SELECT DISTINCT database_name
FROM v_query_stats
WHERE server_id = $1
AND   collection_time >= $3        -- now - 7 days
AND   delta_execution_count > 0

That's a scan of every raw query_stats row the server has, to learn which databases executed anything. Across the fleet, one refresh reads about the whole raw query_stats table.

It also doesn't measure what it says. Raw query_stats retention on this store is 4 days. So "no executions in 7 days" is really "none in the ~4 days raw still holds", and a database that ran on day 5 or 6 is reported idle. query_stats_db_hourly (and _daily) keep the per-database execution sums for the full window.

Measured

Production SQL Server store A (43 servers, raw query_stats 18 GB across 6 chunks), 2026-09-25, EXPLAIN (ANALYZE, BUFFERS), read-only, one busy server (~1.2 M query_stats rows in the window):

read	exec	buffers	rows
`ServerMetricsSql`, whole statement, one server (cold)	4,377 ms (10.97 s of read I/O summed across workers)	50.3 k (34.8 k read)	1
`idle_dbs` arm alone (warm)	268 ms	43.7 k (32.3 k read)	3
the same question from `query_stats_db_hourly` (`execution_count_sum > 0`, materialized buckets)	32 ms	175	3
`ServerInventorySql` (the fleet list)	10 ms	2.8 k	43

Per refresh: 43 × ~44 k buffers ≈ 1.9 M buffers (~15 GB of buffer traffic) every 30 s, from the idle arm alone. That's a projection from the per-server cost × the fan-out, not an observed load: nobody runs this sub-tab on the store today.

Where (origin/dev)

Darling/PerformanceMonitor.Darling.Viewer/FinOpsTab.Loaders.cs: LoadFinOpsServerInventoryAsync ~:560–600, with the per-server GetServerMetricsAsync ~:582 inside ViewerReadFanOut.Lanes.
Darling/PerformanceMonitor.Darling.Viewer/ViewerDataService.FinOps.Inventory.cs: ServerMetricsSql ~:31 (idle_dbs ~:81–101); GetServerMetricsAsync passes idleCutoff = UtcNow.AddDays(-7).
Darling/PerformanceMonitor.Darling.Viewer/MainWindow.xaml.cs ~~:1173: FinOps refreshes on the fleet timer. Recommendations is exempted, as refresh-on-activation (~~:611–620); FinOps is not.
Darling/PerformanceMonitor.Darling.Storage/TimescaleSupport.cs ~:1981: query_stats_db_hourly carries execution_count_sum per (server, database, hour). The FinOps Workload reads already stitch it (ViewerDataService.FinOps.Workload.cs ~:122–136).

Fix shape

Answer "which databases ran anything in N days" from the rollup: query_stats_db_hourly stitched to its interval-honest successor through RollupCoverage, as FinOps.Workload.cs already does, plus a raw head for the unmaterialized hours (the aggregate is materialized_only). The window then means what it says, and the cost is per database-hour, not per query-snapshot.
One fleet statement instead of 43. The five CTEs group naturally by server_id: 24 h CPU, the latest memory and size snapshots via per-server LATERAL … LIMIT 1 (The fleet overview reads each server's newest sample instead of every retained row, and the web viewer asks for it once per refresh (#3895) #3947's shape), grants, and idle databases. One round-trip per refresh, whose cost doesn't depend on lane scheduling.
Take Server Inventory off the 30 s timer. Its inputs move daily (sizes, editions, idle databases). Refresh on activation and on the Refresh button, as Recommendations already does.
Pins:
- result equality of the idle-database count against the raw read on a seeded store whose raw retention covers the window;
- a case where raw retention is shorter than the window: a database active only on day 6 is not reported idle;
- a source pin that ServerMetricsSql (or its successor) doesn't read v_query_stats/query_stats for a multi-day window.

The second pass measured the rest of the FinOps tab. The "take it off the 30 s timer" point should cover the whole tab, not only Server Inventory.

Utilization, the sub-tab FinOps opens on, runs six reads every 30 s while visible. Store B, the busiest server, EXPLAIN (ANALYZE, BUFFERS), first run:

read (`FinOpsTab.Loaders.cs` ~:187–215)	exec	planning	buffers
`UtilizationEfficiencySql` (24 h)	172 ms	12 ms	2.3 k
`DatabaseSizeLatestSql`	2.6 ms	148 ms	80
`TopResourceConsumersByTotalSql` (24 h, raw tier)	875 ms (620 ms I/O)	17 ms	27.1 k
`TopResourceConsumersByAvgSql` (24 h, raw tier)	175 ms	0.8 ms	the same 27.1 k
`DatabaseSizeSummarySql`	4.1 ms	15 ms	62
`ProvisioningTrendSql` (7 d)	241 ms	36 ms	4.9 k

That's about 1.5 s of execution plus 0.23 s of planning per refresh cold, for figures that move hourly (utilization) or daily (sizes).
The two top-consumer grids aggregate the same 24 h of raw query_stats twice (one GROUP BY database_name would feed both, since the average is total ÷ executions). The Performance Trends tab has the same double read (findings, first pass).
Planning is a real share on a store with 71 database_size_stats chunks: 148 ms to plan a 2.6 ms latest-snapshot read.

Storage Growth (StorageGrowthSql, the three MAX(collection_time) lookups): 20.6 ms + 45.5 ms planning. The lookups are one-chunk DeferredChunkAppend probes. Not a finding.

Storage Growth → database drill (object growth, 30 d, the largest database):

ObjectGrowthSummarySql: 191 ms + 138 ms planning, 22.7 k buffers.
ObjectGrowthSeriesSql: 650 ms + 49 ms planning, 27.9 k buffers.
Both recompute the same bounds CTE (MAX/MIN(collection_time) for server + database). That alone is 89 ms and 21.3 k buffers each time, because no index leads (server_id, collection_time). That's Anomaly detector's "latest two object-stats snapshots" read has no usable index: ~350 MB read per server per pass (15.9 M blocks in 5 h on one store) #4196's class; V142 (add the supporting index instead of scanning the whole fleet's newest chunk (#4196) #4216) adds the index, so re-measure after it deploys.
Computing bounds once and passing both instants to both statements halves the rest.
The drill also refreshes every 30 s while open.

Fix-shape addition: refresh FinOps on activation and on its Refresh button (the Recommendations rule), and fold the two top-consumer reads into one aggregate.

Lane V1 (fix/4226-viewer-timer-reads, PR #4236) took #4226 and read this issue closely but did not implement
it: ran out of budget before building. Handing off the diagnosis so the next pass does not have to re-derive
it.

The existing stitched-routing pattern in ViewerDataService.FinOps.Workload.cs /
ViewerDataService.QueryTrends.cs (RollupCoverage.StitchedRelationSql, built by #4182/#4184) is not a
drop-in fix here. query_stats_db_hourly and its successor query_stats_db_interval_hourly are
materialized_only = true (TimescaleDB's post-2.13 default; TimescaleSupport's own remarks on the
query_stats family confirm this is deliberate), unlike collection_health_hourly (#4226's rollup), which is
materialized_only = false. A plain stitched read misses whatever the aggregate has not refreshed yet. The two
existing stitched readers tolerate that on a 24-hour CPU/IO summary; a boolean "did this database run anything
at all" check should not, since a database active only in the last refresh cycle would read as idle.

So the fix needs a raw supplement on both edges of the routed window: the partial leading hour the bucket grain
cannot slice (the same problem #4226's CollectionHealthRollupSupport.ComposeFleetSql already solves, and
reusable as a pattern), and a trailing unmaterialized-hours supplement that no existing reader in this codebase
has needed yet. That second piece is real design work, not a mechanical port.

The rest of the issue's fix shape (one fleet statement instead of 43 per-server ones; taking Server Inventory
off the 30s timer onto activation-refresh, matching Recommendations) is unaffected by this and should still be
straightforward.

Recommend re-queuing as its own lane with a fresh budget rather than folding into another issue's lane.

Add live equality pins for the FinOps top-consumers and object-growth…

#4227: fix idle_db_count going NULL when a server has zero idle datab…

Lite FinOps: merge Utilization top-consumers into one statement, one …

#4227: update the fan-out census pin for one fewer per-server loop

FinOps Server Inventory: one fleet query instead of one per server (#…

Lower RollupCoverageRoutingTests floor from 6 to 5 callers (#4240)

FinOps refresh: take the FinOps tab off the 30s timer, merge its doub…

Closed by #4240 (merged).

Job History: GROUP BY join replaces the per-job window function, tab …

Navigation Menu

Repository navigation

WPF FinOps Server Inventory re-runs a 7-day raw query_stats DISTINCT for every server on the 30 s timer (~43 × 44 k buffers per refresh); query_stats_db_hourly answers it in 32 ms / 175 buffers #4227

Problem

Measured

Where (origin/dev)

Fix shape

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

WPF FinOps Server Inventory re-runs a 7-day raw query_stats DISTINCT for every server on the 30 s timer (~43 × 44 k buffers per refresh); query_stats_db_hourly answers it in 32 ms / 175 buffers #4227

Description

Problem

Measured

Where (origin/dev)

Fix shape

Activity

erikdarlingdata commented on Sep 25, 2026

erikdarlingdata commented on Sep 25, 2026

erikdarlingdata commented on Sep 25, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions