When does SQL Server warn about an Excessive Memory Grant? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

When does SQL Server warn about an Excessive Memory Grant?

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

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

  [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

1 Answer