sql-server add tag
Paul White (imported from SE)
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][1]][1]  

[![SSMS properties][2]][2]  

### Plan Explorer

[![Plan Explorer][3]][3]  

### Showplan xml

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

  [1]: https://i.stack.imgur.com/99Lwu.png
  [2]: https://i.stack.imgur.com/1oGc6.png
  [3]: https://i.stack.imgur.com/0lJLp.png
Top Answer
Paul White (imported from SE)
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][1] 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][2]:

* 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

  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-resource-semaphores-transact-sql
  [2]: https://support.microsoft.com/en-us/help/3172997

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.