SSMS Memory Grant Info

May 11, 2020

Over some time, SQL Server Management Studio has expanded memory grant information. Memory grant provides memory grant estimate as well as actual runtime memory grant information. In this blog post, I will explain each element of ‘Memory Grant Info’, so we can get a better understanding of what those numbers mean?

I am using this document as reference: http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2017/showplanxml.xsd

What is Memory Grant?

Query memory grant (a.k.a. query work buffer) is a part of server memory used to store temporary row data while sorting and joining rows. It is called “grant” because the server requires those queries to “reserve” before actually using memory. This reservation improves query reliability under server load, because a query with reserved memory is less likely to hit out-of-memory while running, and the server prevents one query from dominating entire server memory.

Understanding SQL server memory grant by Jay Choe

DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory.
(Amount needed to store all temporary rows in memory. This depends on the cardinality estimate, expected number rows and average size of row). This is called additional because a query can survive lack of such memory by storing part of temporary rows on hard disk. A query is not guaranteed to have the full amount if the total exceeds the preset limit.)

GrantedMemory: Initial grant of memory in KB.
I am adding a quote from Brent Ozar (blog|twitter):

How much this execution of the query actually got. This can vary between execution to execution depending on the server’s available workspace memory at the time. Yes, dear reader, this is one of the reasons why sometimes your query is slow, and sometimes it’s fast. This value is not cached as part of the plan.

An Introduction to Query Memory by Brent Ozar

GrantWaitTime: Time in seconds if the query has to wait for a successful memory grant.

MaxQueryMemory: Maximum memory in KB allowed for a single query. When we see a memory grant warning often time conclusion is made that server is short of memory. In fact most of the time the reason is a low estimated number of rows. You can prove that by seeing this value is higher than the requested memory.

The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008 (Resource Governor)

Understanding SQL server memory grant by Jay Choe

MaxUsedMemory: Maximum memory in KB used by the query.

RequestedMemory: Memory in KB which the query requests the memory manager to grant. This can be smaller than sum of RequiredMemory and DesiredMemory if it exceeds the maximum allowed for single query.

RequiredMemory: Required memory in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialRequiredMemory. A query will not start unless it can reserve this amount of memory. You can use sys.dm_exec_query_memory_grants DMV to identify which queries are waiting for the required memory grant.

SerialRequiredMemory: Required memory in KB if the query runs in serial mode. The query will not start without this memory.

SerialDesiredMemory: Memory estimated to fit intermediate results in KB if the query runs in serial mode.