When does SQL Server warn about an Excessive Memory Grant? What are the conditions that produce an "Excessive Grant" execution plan warning?


  The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, Final 5128 KB, Used 16 KB.


SSMS

  

  

Plan Explorer

  

Showplan xml

&lt;Warnings&gt;
    &lt;MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" /&gt;
&lt;/Warnings&gt;
To produce this warning:


The maximum used memory must be less than 5% of the granted memory; AND
The query must use the regular (not small) resource semaphore


To use the regular resource semaphore the query must:


Have granted memory over 5MB (5120 KB, 640 x 8KB pages); OR
Have a total estimated plan cost of over 3 units and not be a trivial plan


Server version requirements:


SQL Server 2014 SP2 (12.0.5000) or later
SQL Server 2016 SP1 (13.0.4001) or later
SQL Server 2017 RTM (14.0.1000) or later