SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element – but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes.
Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan:
<MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="11016" RequiredMemory="47368" DesiredMemory="52808" RequestedMemory="52808" GrantWaitTime="0" GrantedMemory="52808" MaxUsedMemory="4312" />
The unit of measurement is in KB – and in the below perfmon screenshot you can see that the “52808” value in the GrantedMemory attribute matches the Granted Workspace Memory (KB) performance monitor counter:
Examining the latest Showplan Schema (last updated March 2012) under the MemoryGrantType section, you’ll find a documentation element which states the following:
“Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are estimated during query compile time for serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism.”
To test the serial plan related memory stats, I re-ran my original query using MAXDOP 1 and got the following results in my actual execution plan:
<MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="1600" RequiredMemory="1536" DesiredMemory="1600" RequestedMemory="1600" GrantWaitTime="0" GrantedMemory="1600" MaxUsedMemory="328" />
The serial memory (desired and required) numbers shifted downwards. My original MemoryGrantInfo was for a query referencing a columnstore index executing in batch mode (with parallelism, as required).
Here is the original plan:
By capping the parallelism to a MAXDOP 1, the QO still chose to reference the columnstore index scan operator, but the plan operator requirements changed and thus so did the overall memory requirements (including a change from a Hash Match to a Merge Join):
The Granted Workspace Memory (KB) perfmon counter – which showed 1600 KB for the serial plan – matched the 1600 value for SerialDesiredMemory, DesiredMemory and RequestedMemory.
While the MemoryGrantInfo element doesn’t replace the need for the sys.dm_exec_query_memory_grants DMV when trying to evaluate request level concurrent memory grant activity, having this additional information in the execution plan is much more convenient when you’re trying to identify requirements scoped to a specific query.