25

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>
Paul White
  • 94,921
  • 30
  • 437
  • 687

2 Answers2

21

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
Paul White
  • 94,921
  • 30
  • 437
  • 687
1

How to Resolve the Warning:

At the time of this writing, Googling this Warning yielded only 7 results.   Just 7.
Luckily one of these links (sqlservercentral.com) had a kind user (by the name of DesNorton)
who had this to say, while helping someone who was running into this same warning:

"The query memory grant detected 'ExcessiveGrant', which may impact the reliability.
Grant size: Initial 8360 KB, Final 8360 KB, Used 40 KB."
- This is related to the table definitions. SQL needs memory to process a query.
To figure out how much memory to request, SQL uses the meta-data and not the actual data.
It calculates the size of 1 record based on the field definitions, and multiplies that
by the estimated number rows that it will get back (based on statistics).
In this case, it estimated that it would require 8360KB (or more) of RAM, but due to the actual number of records as well as the empty space in the actual records, it only required 40KB of RAM.
This is a waste of memory, which could have been allocated to another query.

Very succintly put.
This immediately made me wonder if Updating the Statistics on my Tables would resolve the issue.
I hunted down any Statistics that had a difference in Rows_Sampled compared to the number of Rows in the Table and Updated them with FullScan on both Columns and Index, like so:

UPDATE STATISTICS [dbo].[YourTableName] WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].[YourTableName] WITH FULLSCAN, INDEX

This is, admittedly, and overboard approach, but the dev database was small and I was not about to work my way through each and every Stat to find out exactly which one would help my query.

SqlShack.com has a great write-up about the "Row Mode Memory Grant",
    however, they stop short of offering a solution when you encounter it.

MikeTeeVee
  • 273
  • 3
  • 6