[BUG] FinOps provisioning status reports UNDER_PROVISIONED for every healthy server: the memory ratio compares Total to Target Server Memory, which converge at 1.0

Surfaced by #2231, where a user running both products side by side sees every server as UNDER_PROVISIONED in Darling and RIGHT_SIZED in the Full Dashboard.

Measured

The rule in ViewerDataService.FinOps.Utilization.cs:

else if (p95Cpu > 85 || memRatio > 0.95m)
    status = "UNDER_PROVISIONED";

with

CAST(total_server_memory_mb AS DECIMAL(10,2)) / NULLIF(target_server_memory_mb, 0) AS memory_ratio

Against a 42-server production fleet, latest memory_stats row per server:

servers=42   ratio_gt_0.95=42   ratio_eq_1=3
median=1.0000   min=0.9997   max=1.0002

Every server trips it. Not a threshold that is slightly too tight — the metric has no useful spread at all. Four decimal places of noise around 1.0.

Why the metric cannot work here

total_server_memory_mb and target_server_memory_mb are the perfmon Total Server Memory (KB) and Target Server Memory (KB) counters. Target is the memory SQL Server wants; Total is what it currently commits. A warmed instance that is not under external pressure sits at Total == Target, so the ratio is 1.0 whenever things are healthy.

The trigger is therefore also backwards. Memory pressure appears as Total below Target — SQL Server wanting more than it has — which is a ratio under 1. As written, the rule reads the healthy steady state as starvation and would go quiet during actual pressure.

Divergence from the Full Dashboard, which does not have this problem

report.finops_utilization_efficiency (install/54_create_finops_views.sql) computes the same-named ratio from a different pair:

	numerator	denominator
Full Dashboard	`SUM(pages_kb)` from `sys.dm_os_memory_clerks`	`committed_target_kb` from `sys.dm_os_sys_info`
Darling	perfmon Total Server Memory	perfmon Target Server Memory

Memory-clerk pages against the committed target normally lands well below 1, which is why the old verdict behaves sensibly. The CPU half is identical in both (24-hour window, p95 over cpu_utilization_stats), so CPU is not implicated.

Second, separate divergence

The old view raises UNDER_PROVISIONED on a third condition Darling omits:

OR sl.worker_ratio > 0.8

So Darling would also miss a genuinely worker-thread-starved server that the Full Dashboard flags. Worth fixing alongside, since max_workers_count and current_workers_count are already selected by UtilizationEfficiencySql and read into the row — the columns are there, the term just is not.

Fix shape — needs a decision, not just a number

The threshold is not the bug; the signal is. Options, roughly ascending in fidelity:

Port the old pair. Compute the ratio from memory clerks vs committed target so the two products agree. Restores parity, but inherits whatever the old ratio's real meaning is.
Invert and re-aim it. Treat sustained Total < Target as the pressure signal (ratio below some floor, over a window rather than one sample), which is what the counters actually say.
Use signals that mean memory pressure. Memory grants pending, sustained low Page Life Expectancy, or RESOURCE_SEMAPHORE waits — none of which this view currently reads, all of which the store already collects.

(3) is the honest answer to "is this server short on memory"; (1) is the smallest change that stops the false alarm and restores agreement between the products.

Also worth noting for whoever takes it: the verdict reads a single latest memory_stats row, so it is one sample deep and cannot distinguish a transient from a trend regardless of which metric it uses.

Affects

Both SKUs' FinOps tab (Lite/Services/LocalDataService.FinOps.Utilization.cs carries the same rule shape — needs the same check) and ProvisioningTrendSql's per-day status in the same file, which applies the identical predicate.

Checked the Lite side rather than leaving it as a suspicion in the "Affects" section: it is not "needs the same check" — the defect is confirmed identical in all four sites.

file	line	ratio source	trigger
`Darling/.../ViewerDataService.FinOps.Utilization.cs`	99	`total_server_memory_mb / target_server_memory_mb`	`memRatio > 0.95m`
same, `ProvisioningTrendSql` path	172	same	same
`Lite/Services/LocalDataService.FinOps.Utilization.cs`	92	same	same
same, trend path	169	same	same

So this is a shared-logic bug duplicated four times rather than a Darling-only regression, and any fix has to land in both SKUs to keep them in parity.

One extra detail from the Lite trend path worth noting for whoever fixes it: it aggregates as AVG(total_server_memory_mb / target_server_memory_mb) per day. Averaging a ratio that is pinned at 1.0 still yields 1.0, so the daily trend is also flat-UNDER rather than merely inheriting a bad instantaneous read — the trend view cannot show a server ever leaving that state.

Both OVER_PROVISIONED arms also gate on memRatio < 0.5, which the same measurement rules out reaching (fleet min 0.9997). So on current metrics the verdict is effectively pinned: it can only ever say UNDER_PROVISIONED, or RIGHT_SIZED when CPU is low enough to fail the p95 test — and never OVER_PROVISIONED at all, no matter how idle the server is. That is worth stating in the fix's acceptance criteria: whatever replaces the memory term has to restore a reachable OVER_PROVISIONED.

Why the apps compute this differently from the Full Dashboard

Traced it, because "why is there a second formula at all" is the more useful question than "which threshold is right".

The split is not Darling vs Lite — those two are byte-identical. It is the apps vs the SQL-view dashboard:

	numerator	denominator
`report.finops_utilization_efficiency` (views)	`SUM(pages_kb)` over all of `sys.dm_os_memory_clerks`	`dm_os_sys_info.committed_target_kb`
Darling + Lite (app code)	perfmon Total Server Memory (KB)	perfmon Target Server Memory (KB)

The denominators are the same metric. Perfmon's Target Server Memory (KB) is documented as the ideal amount of memory the instance can consume — the same value committed_target_kb reports. So that half of the port was faithful.

The numerator is what changed, and it changed meaning. The old one is the memory attributed to clerks; the new one is all committed server memory. Committed converges on committed-target the moment an instance is warmed, which is why the ported ratio pins at 1.0 (measured: 42/42 servers, median 1.0000, min 0.9997, max 1.0002) while clerk-pages-over-target sits meaningfully below 1.

It reads like a convenience substitution rather than a deliberate redesign: memory_stats already had a total_server_memory_mb column, so using it avoided a join to memory_clerks. Nothing in the code records the trade, and the threshold was carried over unchanged from a formula whose range it no longer matches.

Restoring parity is not quite free — worth knowing before someone tries

memory_stats, per the shared collector, holds: total_physical_memory_mb, available_physical_memory_mb, total_page_file_mb, available_page_file_mb, system_memory_state, sql_memory_model, target_server_memory_mb, total_server_memory_mb, buffer_pool_mb, plan_cache_mb, max_workers_count, current_workers_count. No clerk total.

The clerk data lives in memory_clerks(clerk_type, memory_mb) — but that collector is deliberately truncated:

SELECT TOP (25)
    clerk_type = mc.type,
    memory_mb  = CONVERT(decimal(18,2), SUM(mc.pages_kb) / 1024.0)
FROM sys.dm_os_memory_clerks AS mc
GROUP BY mc.type
HAVING SUM(mc.pages_kb) > 1024

Top 25 clerk types, and only those above 1 MB. So SUM(memory_mb) from the store is not the view's SUM(pages_kb) — it systematically undercounts by the tail. Close, since the tail is small, but an approximation rather than the same number.

That gives three honest options rather than two:

Approximate port — SUM(memory_clerks.memory_mb) / memory_stats.target_server_memory_mb. Restores the verdict's behavior and agreement with the Full Dashboard, at the cost of a top-25 truncation in the numerator and a join whose row may be missing if the two collectors' cadences differ.
Store the true total — add one scalar to MemoryStatsCollector (the untruncated clerk sum, or dm_os_process_memory.physical_memory_in_use_kb) so the old formula becomes exactly computable. One column plus a migration; makes (1) exact instead of approximate, and keeps it a single-table read.
Re-aim the signal — memory grants pending, sustained low PLE, RESOURCE_SEMAPHORE waits. The only option that actually answers "is this instance short on memory", and none of it needs the ratio.

(2) is the smallest change that makes the products agree and be correct. (3) is still the better long-term answer, and the two aren't exclusive: (2) can land first to stop the false alarm.

Whatever is chosen, the acceptance criteria from my earlier comment stand: OVER_PROVISIONED has to become reachable again — its arm needs ratio < 0.5, which the current metric can never produce.

Correction: porting the old formula does NOT fix this. Measured.

I recommended option (2) — store the untruncated clerk total so the Full Dashboard's formula becomes exactly computable — as "the smallest change that makes the products agree and be correct". I tested that against the fleet before anyone spent time on it, and it is wrong.

Latest memory_stats and latest memory_clerks row per server, 42 servers:

clerks collected fleet-wide: 42 servers, avg 25.0 clerk types per collection

CURRENT ratio (total_server_memory / target):  median 1.0000            42/42 > 0.95
PORTED  ratio (SUM(clerk mb)   / target):      median 0.9662            42/42 > 0.95
                                               min 0.9566  max 0.9712   0/42 < 0.5

The ported numerator is ~96.6% of target on this fleet. That clears 0.95 on every single server, so the verdict stays pinned at UNDER_PROVISIONED and OVER_PROVISIONED stays unreachable. Option (1) fails for the same reason, and option (2) would be marginally worse: recovering the truncated tail can only push the ratio up, further past the threshold.

Note also avg 25.0 clerk types per collection — the TOP (25) cap is binding on every server, not occasionally, so the truncation I flagged is always active. It just does not matter here, because even the truncated sum is too high.

What this actually means

The threshold is as broken as the metric. > 0.95 cannot separate healthy from starved for either numerator on a real fleet, because both numerators are large fractions of the target by construction. Agreement with the Full Dashboard is therefore not the goal — the reporter's Azure SQL DB happens to land below 0.95 with the old formula, but that is a property of their workload, not evidence the old rule discriminates.

So of the three options I listed, only (3) re-aim the signal survives measurement:

memory grants pending (memory_stats does not carry it; perfmon_stats has Memory Grants Pending)
sustained low Page Life Expectancy — noting PLE is deliberately not collected, per DarlingMcpTrendTools' own not-collected hint, so this needs a decision rather than a query
RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_QUERY_COMPILE waits from wait_stats
system_memory_state and sql_memory_model, which memory_stats already carries and nothing currently reads — the cheapest place to start, since SQL Server itself reports its memory state there

A ratio-based rule could still work if it were aimed at the gap rather than the level — e.g. target - total sustained above zero over a window means the instance wants memory it cannot get — but that needs a window, not the single latest row this verdict reads, and it should be validated against a fleet before shipping. I would not put a number on it without that.

Acceptance criteria, updated: whatever replaces the term has to produce more than one distinct verdict across 42 healthy production servers. That is the bar the current rule and both ports fail.

Designed from the fleet instead of from opinion

Measured every candidate signal the store already carries, across 52 servers. Two of my earlier claims need correcting, and the result is a rule with numbers behind every threshold.

What the store already has that the verdict ignores

cpu_scheduler_stats carries physical_memory_pressure_warning, system_memory_state_desc, available_physical_memory_kb, worker_thread_exhaustion_warning, runnable_percent. memory_grant_stats carries waiter_count, timeout_error_count_delta, forced_grant_count_delta, and the semaphore's own target/granted/used_memory_mb. wait_stats has RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE. None of it is read by this verdict.

The measurements (24h unless noted)

signal	result
`Memory Grants Pending`	0 nonzero of 1,000,560 rows (full retained history, from 2026-07-17)
grant `waiter_count` / timeouts / forced	0 / 0 / 0 of 2,938,711 rows — ever
`RESOURCE_SEMAPHORE` wait ms	present on 1 server, 0 ms
grant utilization (`granted/target`)	p50 0.5%, p95 12.0%, max 18.8%
OS headroom (min per server)	p05 9.6%, p50 10.0%, p95 10.6%, max 31.9%
`physical_memory_pressure_warning`	ever-true on 28 of 52
`system_memory_state_desc`	uniformly "Available physical memory is high"
CPU p95 per server	min 0.0, p25 6.5, p50 11.0, p95 48.6, max 51.0
worker ratio (max per server)	p50 0.246, max 0.635; `> 0.8` on 0; exhaustion warnings 0

Correction 1: my acceptance bar was aimed at the wrong term

I wrote twice that any replacement must "produce more than one distinct verdict across 42 healthy servers". That is wrong for the memory term. On a fleet with no measurable memory pressure — and the numbers above say this fleet has none, across 2.9M grant rows and 1M counter rows — a correct memory term is uniformly silent. Demanding spread from it would just be demanding a different false positive.

The spread has to come from CPU, and it is already there: avg < 15 AND max < 40 fires on 35 of 52 servers, so removing the memory term alone turns this feature from one useless verdict into a 35/17 split that actually answers "which servers can be downsized" — the entire point of the report.

Correction 2: `physical_memory_pressure_warning` is not usable as-is

28 of 52 ever-true looks like a promising signal until you see that system_memory_state_desc says "Available physical memory is high" on every one of them, and that the OS-headroom minimum clusters at 9.6–10.6% for 95% of servers. A flag that trips when a tightly-clustered value crosses a nearby line is marginal noise, not evidence. It would recreate the current bug with a different column.

The rule the data supports

UNDER_PROVISIONED — any of:

cpu_p95 > 85 — 0 servers today, threshold unchanged and still headroom (fleet max p95 is 51.0)
worker_ratio > 0.8 or worker_thread_exhaustion_warning — 0 today (fleet max 0.635); this restores the term Darling dropped and the Full Dashboard has always had
memory, from signals that mean it: waiter_count > 0, or a nonzero timeout/forced-grant delta, or Memory Grants Pending > 0, or RESOURCE_SEMAPHORE wait time above a small floor — all 0 today, correctly

OVER_PROVISIONED — avg_cpu < 15 AND max_cpu < 40 and no memory pressure and grant utilization under 50% (fleet max 18.8%, so this excludes nothing real today but stops a server that is quiet on CPU while straining the semaphore from being called idle).

RIGHT_SIZED — otherwise.

On today's fleet that yields 35 OVER_PROVISIONED / 17 RIGHT_SIZED / 0 UNDER_PROVISIONED, and every threshold above is either unchanged-and-verified-reachable or set with the fleet distribution beside it.

The honest gap

There is no positive control. Nothing in the retained history — 4 weeks, 2.9M grant rows — has ever recorded a grant waiter, a timeout, a forced grant, or a pending grant. So I can show the memory term will stay quiet on a healthy fleet, but I cannot show from data that it fires when pressure arrives. That has to be established by construction instead: a test that drives the predicate with fabricated pressure rows, which is cheap and does not need a fleet. Worth saying out loud rather than implying the alarm is validated.

Merge pull request #2251 from erikdarlingdata/fix/2246-provisioning-v…

Fixed on dev in #2251 and #2253.

The verdict now comes from a single shared ProvisioningVerdict in Common, and every threshold in it was measured rather than chosen:

term	fleet distribution	limit
CPU p95 per server	p50 11.0, max 51.0	`> 85`
worker ratio	p50 0.246, max 0.635	`> 0.8`
grant utilization	p50 0.5%, max 18.8%	`< 50` to be idle
grant waiters / timeouts / forced	0 across 2,938,711 rows, entire history	any `> 0`

Prototyped against the live store: 25 OVER / 17 RIGHT / 0 UNDER where the old rule gave 42 UNDER.

Two things worth recording. Porting the Full Dashboard's formula would not have fixed it — SUM(memory_clerks)/target runs median 0.9662 here, still above 0.95 on 42 of 42 — so the threshold was as broken as the metric, and agreement with the old view was never the goal. And there were six copies of the rule, not four: review caught that ServerMetricsSql in both apps still decided in SQL, which feeds the Server Inventory grid — the screen the field report was actually looking at. All six now route through the one predicate, and the grid's SQL is pinned to assert the status literals are absent.

The memory term is silent on this fleet, and that is correct: nothing here has memory pressure. Its positive controls are built in tests instead, one pressure input at a time, because the fleet cannot supply them. Ships with the next release.

CHANGELOG entry for #2254

Drop the dead memory_ratio from the two inventory reads

CHANGELOG: backfill the five shipped changes that had no entry

Navigation Menu

Repository navigation

[BUG] FinOps provisioning status reports UNDER_PROVISIONED for every healthy server: the memory ratio compares Total to Target Server Memory, which converge at 1.0 #2246

Measured

Why the metric cannot work here

Divergence from the Full Dashboard, which does not have this problem

Second, separate divergence

Fix shape — needs a decision, not just a number

Affects

Why the apps compute this differently from the Full Dashboard

Restoring parity is not quite free — worth knowing before someone tries

Correction: porting the old formula does NOT fix this. Measured.

What this actually means

Designed from the fleet instead of from opinion

What the store already has that the verdict ignores

The measurements (24h unless noted)

Correction 1: my acceptance bar was aimed at the wrong term

Correction 2: `physical_memory_pressure_warning` is not usable as-is

The rule the data supports

The honest gap

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

[BUG] FinOps provisioning status reports UNDER_PROVISIONED for every healthy server: the memory ratio compares Total to Target Server Memory, which converge at 1.0 #2246

Description

Measured

Why the metric cannot work here

Divergence from the Full Dashboard, which does not have this problem

Second, separate divergence

Fix shape — needs a decision, not just a number

Affects

Activity

erikdarlingdata commented on Aug 13, 2026

erikdarlingdata commented on Aug 14, 2026

Why the apps compute this differently from the Full Dashboard

Restoring parity is not quite free — worth knowing before someone tries

erikdarlingdata commented on Aug 14, 2026

Correction: porting the old formula does NOT fix this. Measured.

What this actually means

erikdarlingdata commented on Aug 14, 2026

Designed from the fleet instead of from opinion

What the store already has that the verdict ignores

The measurements (24h unless noted)

Correction 1: my acceptance bar was aimed at the wrong term

Correction 2: physical_memory_pressure_warning is not usable as-is

The rule the data supports

The honest gap

erikdarlingdata commented on Aug 14, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions

Correction 2: `physical_memory_pressure_warning` is not usable as-is