18

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

SSMS plan

SSMS properties

Plan Explorer

Plan Explorer

Showplan xml

<Warnings>
    <MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>
19

To produce this warning:

  1. The maximum used memory must be less than 5% of the granted memory; AND
  2. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.