Difference between 'GrantedMemory' and 'MaxQueryMemory' attributes in Showplan XML

Question

Following new attribute to the Showplan XML schema SQL Server 2014 or 2016 was introduced by KB3170112.

MaxQueryMemory under MemoryGrantInfo: Maximum amount of memory available for individual query grant in KB, if the query requires memory to run.

Attribute GrantedMemory existed before above was announced. Now both are there in Showplan XML.

Here is an example:

From above picture if I add the value of RequestedMemory and RequiredMemory total is =62,864 KB. Where is the value 295,344 KB for MaxQueryMemory coming from or what does that value really mean?

I also looked at 2017 Schema, which has below definition.

MaxQueryMemory: Maximum memory in KB allowed for single query.

Answer 1

It's a snapshot of the same information that you can get from sys.dm_exec_query_resource_semaphores. I believe that it's target_memory_kb for the regular resource semaphore multiplied by the maximum query grant percent for the Resource Governor group that the query is executing in. If RG is not enabled then just use the default of 25%.

On the servers that I looked at, I generally see about 28% of server memory not available for query memory grants. So once I've run a workload the max query grant that's available for a query is 0.25 * 0.72 * MaxServerMemory.

Here's a query that you can run that finishes almost instantly but asks for a large memory grant:

DECLARE @zero INT = 0;

WITH CTE AS (
    SELECT high
    FROM master..spt_values
    WHERE @zero = 1
)
SELECT *
FROM CTE t1
CROSS JOIN CTE t2
CROSS JOIN CTE t3
ORDER BY t1.high + t2.high + t3.high;

If I force that query to timeout then I get the following in the actual plan:

MaxQueryMemory does not change as a result of the timeout. It isn't directly affected by other queries currently executing and using query memory. It's indirectly affected by anything that changes target memory KB for the resource pool.

This information could be useful if you have a bunch of different Resource Governor pools and groups and want to validate that a query goes to the correct place. It can also provide context to the total size or configuration of a server that you don't have access to. It's another way to tell if a query timed out waiting for a memory grant, but there are already other ways to do that. The intended use case may be to give more information about performance problems caused by target memory changing quite a bit on servers, but I've never seen that happen personally. Maybe it can happen on servers with multiple instances of SQL Server running?

Answer 2

I quote from same Support Article Update to expose maximum memory enabled for a single query in Showplan XML in SQL Server 2014 or 2016

MaxQueryMemory under MemoryGrantInfo: Maximum amount of memory available for individual query grant in KB, if the query requires memory to run.

Which means maximum amount of memory which can be granted to the query in "current" situation if the query, during processing, requests memory. This value is calculated when query ran considering memory requirements of other queries running and the system memory used. This is useful in troubleshooting a slow query which is running slow because statistics are skewed and optimizer prepares sub optimal plan which forces query to request memory different from what it would actually need while running. When it requests much higher than what is asked for then query becomes super slow because it was not granted memory which it needed. This also helps to understand if there is a memory pressure on the system.

GrantedQueryMemory OTOH means Total amount of memory "actually" granted in kilobytes. Can be NULL if the memory is not granted yet. The MaxQueryMemory signifies what maximum can be granted.

A worth reading blog. Understanding Query Memory Grants