Skip to main content
added 198 characters in body
Source Link
Shanky
  • 19.1k
  • 4
  • 36
  • 58

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

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

A worth reading blog. Understanding Query Memory Grants

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

Source Link
Shanky
  • 19.1k
  • 4
  • 36
  • 58

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

A worth reading blog. Understanding Query Memory Grants