Excessive Memory Grant (rule 9) never fires for a grant that used none of it

[erikdarlingdata]

A plan analysis fix from PerformanceStudio (PS) that PerformanceMonitor (PM) still needs. It comes from PS PR #608, which merged after the PS-to-PM sync in #4511 was finished.

Summary

Rule 9 (PlanAnalyzer.cs:369) looks for an excessive grant only when grant.GrantedMemoryKB > 0 && grant.MaxUsedMemoryKB > 0. A grant that used none of its memory has MaxUsedMemory="0", so the rule skips it. A grant of 1 GB or more that used nothing is the largest possible waste, and PM gives no Excessive Memory Grant finding for it.

The parser cannot tell "used none" from "not reported". ShowPlanParser.cs:650 reads the attribute with ParseLong, and ParseLong (ShowPlanParser.cs:2094) returns 0 for a missing attribute. MemoryGrantInfo (PlanModels.cs:494) has no flag for it. So the rule needs the > 0 check, and that check hides the used-none case.

SQL Server does write a real 0. PS measured it on SQL Server 2025 (build 17.0.4045.5). A sort of dbo.Posts by Body that returned no rows got GrantedMemory="11261504" and MaxUsedMemory="0".

PM's code does not show a plan source that has GrantedMemory but no MaxUsedMemory. The defect is the used-none case, not a missing attribute.

PerformanceStudio

erikdarlingdata/PerformanceStudio@11aa46d (erikdarlingdata/PerformanceStudio#608) adds MemoryGrantInfo.HasMaxUsedMemory. It is true when the plan XML has a MaxUsedMemory attribute. The parser sets it with long.TryParse on that attribute. Rule 9 (Rule09_MemoryGrant) now fires when all of these hold:

• The grant is at least 1 GB.
• HasMaxUsedMemory is true.
• The query used 0 KB, or the grant is at least 10 times the use (the old test).

For 0 KB used, the message reads "Granted 10,998 MB but the query used none of it. The unused memory is reserved and unavailable to other queries." It has no ratio and no division. A plan with no MaxUsedMemory attribute does not fire. The adaptive join note (#4531) is added to both messages.

Port

Add HasMaxUsedMemory to MemoryGrantInfo (PlanModels.cs:494). Set it at ShowPlanParser.cs:650 with the same long.TryParse call. Then change the condition at PlanAnalyzer.cs:369 and the message at line 376 to match Rule09_MemoryGrant in PS. PS left the other places that show MaxUsedMemoryKB as they were, so this item does too. Size S.

One PM plan source needs a test first. The query snapshot collector stores a live_query_plan from sys.dm_exec_query_statistics_xml for a running query (QuerySnapshotsCollector.cs:406). Lite's plan view for a snapshot row tries that live plan first (ServerTab.Plans.cs:349).

PS notes that a plan captured while a query still runs might report MaxUsedMemory="0" before the query uses its grant. If that happens, Rule 9 reports the grant as unused. PS did not test this (see "Not done" in erikdarlingdata/PerformanceStudio#608). We recommend a test on a live plan in PM before the used-none message ships. A false finding on a running query misleads the reader.

At 4c6dfe10. dev has no later change to PerformanceMonitor.PlanAnalysis.

[erikdarlingdata]

Closed by the watcher: delivered in PR #4705, merged to dev.