SQL SERVER – MemoryGrantInfo Property Explanation

3 years ago

I have been writing about the Memory Grant Feedback feature for a while on this blog and I am extremely delighted to see lots of questions and interactions on this topic. Today we will discuss various properties of MemoryGrantInfo in this blog post. If you want to learn more about Memory Grant Feedback, I suggest you read the following blog posts before you continue reading this blog.

Today we will discuss the various elements of the MemoryGrantInfo Property.

First, go to any execution plan and click on the left-most SELECT operator. Right Click over it and go to the property. Now this will open the separate property window on the right slide. In this window further example the property MemoryGrantInfo. Right under this property, there will be many different properties.

Let us understand what each element means.

Desired Memory: This is the total desired memory for the query to execute. If the plan is a parallel plan, this value will have an added value that is needed on the top of the Required Memory. In our case, the example the plan is serial, so the value of this property will be the same as Required Memory.

Granted Memory: This is the amount of memory that was finally granted to the query by the SQL Server Engine from all the available memory.

Grant Wait Time: This time indicates how much the query had to wait before it was granted the memory to execute. This is the amount of time in milliseconds. You can read more about this topic by searching on the internet about the DMV sys.dm_exec_query_memory_grants.

Is Memory Grant Feedback Adjusted: This indicates the current state of the memory grant feedback for the query. There are five different states for this configuration. Here are the five different values: NoFirstExecution, YesAdjusting, YesStable, NoAccurateGrant, NoFeedbackDisabled. Each of the values has different meaning and I have previously blogged about this in detail over here: Different Status of Is Memory Grant Feedback Adjusted.

Last Requested Memory: This indicates the granted memory in Kilobytes (KB) from the previous execution of the same query or statement. If you see the status of the is memory grant feedback adjusted property to YesAdjusting, it is quite possible that Last Requested Memory may be different from the currently Granted Memory.

Max Query Memory: This indicates the maximum amount of memory available for individual query grants. This value depends on the overall memory consumptions of the various SQL Server components and keeps on changing for every query. I have never seen SQL Server granting more memory to query which is greater than Max Query Memory.

Max Used Memory: This value represents how much memory the query actually used during the execution irrespective of what was granted to the query and how long it actually had to wait to execute the query.

Requested Memory: This value represents the amount of memory asked from the resource semaphore. When any query is executed SQL Server Engine checks if the desired memory exceeds the Max Query Memory or not, if it exceeds, then it reduces the requested memory till it fits the limit of Max Query Memory.

Required Memory: This indicates the total required memory for the query to start executing. If the query is running on a single degree of parallelism, the value of this will be the same as the serial required memory. If the query is running on multiple degrees of parallelism the value of this will be equal to (Serial Required Memory) * (Degree of Parallelism) + Parallelism Exchange Operators.

Serial Desired Memory: This indicates the sum of all the memory desired to execute the query based on the cardinality and data size estimation while it is running on a single degree of parallelism. This value calculated the query compiled time and usually the same or more than the Serial Required Memory.

Serial Required Memory: This is the minimum amount of memory required for the query to start execution while it is running on a single degree of parallelism. This memory is absolutely needed to create the internal data structure for the memory consuming operators.

Well, that’s it. I think we have discussed about this subject in detail. Let me know if you have any questions about this topic. There are many such topics, we discuss while working together on Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)