The Availability Group read finds each server's newest snapshot by aggregating every retained AG row: 2.8 s and 441 k buffers for 398 rows; a two-step read returns the same rows in ~3 ms #4228

Problem

Both AG reads, replica states and database-replica states, find "the newest snapshot per server" the same way:

JOIN (SELECT server_id, MAX(collection_time) AS max_collection_time
      FROM ag_database_replica_states
      GROUP BY server_id) AS latest
  ON d.server_id = latest.server_id AND d.collection_time = latest.max_collection_time

The inner aggregate has no time bound, so it reads every row in retention for every server. The outer join then probes every chunk for the found instants. The cost tracks retained AG history × fleet size, while the answer is one snapshot per server.

The same statement text runs in both SKUs:

• WPF viewer: the AG tab re-reads it on every fleet-timer tick (30 s) while visible. While the tab is hidden, the same tick runs it as a probe to decide whether to reveal the tab.
• Service: /api/ag runs it on every web page load (Web viewer: every page load downloads the whole Availability Group topology (140–266 KB, 1.5–2.6 s of store time) only to decide whether to show one nav link #4189) and on the AG page's 60 s poll, and MCP get_ag_health runs it too.

#4189 covers not calling the read for the nav probe and memoizing it. This issue is the read's own cost, which every remaining caller still pays.

Measured

Production SQL Server store A (43 servers; ag_database_replica_states is 478 MB across 31 daily chunks), 2026-09-25, EXPLAIN (ANALYZE, BUFFERS), read-only, one at a time:

• The LATERAL-then-join form doesn't help. The join back on a found instant still probes every chunk, because the instant isn't a plan-time bound.
• What makes the read cheap is a literal lower bound on the partition column.
• The web viewer's store-side stats for this statement already showed a mean of 1.56 s and 209 k blocks per call (Web viewer: every page load downloads the whole Availability Group topology (140–266 KB, 1.5–2.6 s of store time) only to decide whether to show one nav link #4189).

Where (origin/dev)

• Darling/PerformanceMonitor.Darling.Viewer/ViewerDataService.AvailabilityGroups.cs: AgReplicaStatesSql ~:31, AgDatabaseReplicaStatesSql ~:64, GetAvailabilityGroupsAsync ~:101.
• Darling/PerformanceMonitor.Darling.Viewer/MainWindow.xaml.cs:
  • the hidden-tab probe on every fleet tick ~:606;
  • the visible-tab refresh via LoadVisibleTabAsync ~:1170, into RefreshAvailabilityGroupsAsync ~:1102.
• Darling/PerformanceMonitor.Darling.Service/Mcp/DarlingAgReader.cs: ReplicaStatesSql ~:76–108, DatabaseReplicaStatesSql ~:112–148 (the same shape, plus an optional $1 server filter). Web /api/ag: DarlingWebEndpoints.cs ~:283.

Fix shape

• Two steps in each reader:

  1. SELECT s.server_id, l.collection_time FROM servers s CROSS JOIN LATERAL (SELECT collection_time FROM ag_database_replica_states WHERE server_id = s.server_id ORDER BY collection_time DESC LIMIT 1) l WHERE s.is_enabled. Ordered descent, 1 chunk per server.
  2. The existing statement with $floor bound on both the inner aggregate and the outer scan, where $floor = the minimum instant from step 1. The same for ag_replica_states.

• Stale servers: a server whose last AG snapshot is days old would drag $floor back. Don't let it:

  • apply a staleness horizon: servers whose newest snapshot is older than it are read on their own, or listed as stale without a snapshot;
  • or run step 2 per server with that server's own instant as the bound.

  Either way, cost follows servers, not retention.

• Share one implementation. The Viewer can't reference the Service assembly. Move the reader into PerformanceMonitor.Darling.Storage (like DarlingQueryStoreClutterReader) so the WPF tab, /api/ag and get_ag_health run the same two statements.

• Pins:

  • result equality against today's statement on a seeded store with several days of AG snapshots, including a server whose AG rows stopped a day ago;
  • a live EXPLAIN assertion that step 2 touches ≤ 2 chunks when every server is current;
  • a source pin that no AG read uses MAX(collection_time) … GROUP BY server_id without a collection_time bound.