As suggested by the title, I like redundancy, bad titles and we’re going to have a brief look into MemoryGrantInfo.
In a previous post, we’ve talked about an awesome feature from the new SQL Server 2019 – Row Mode Adaptive Memory Grant Feedback. We saw this feature in action and briefly explored MemoryGrantInfo, but some fields were not explained. So, let’s recall the MemoryGrantInfo section and analyze its properties:
DesiredMemory: Amount of memory (in kilobytes) estimated to be needed to run the execution plan, based on the cardinality and data size estimation. This value will be cached as part of the plan and will remain the same – if Adaptive Memory Grant Feedback is not enabled.
GrantedMemory: Amount of memory that was actually granted at runtime. This value can be lower than DesiredMemory if the server doesn’t have enough available memory at the moment of the grant. Due to this fact, it’s possible that its value differs across similar executions, depending on the server’s available workspace memory at the time. When the grant isn’t high enough to keep all data in memory it will write it into tempdb – this is also known as a spill and it can degrade query performance. This doesn’t mean that a lower grant will always cause a spill, nor that a spill will always have a substantial impact on performance, nevertheless, that’s definitely something to keep an eye on.
GrantWaitTime: Time (in seconds) that the query had to wait before it was granted the memory to execute. This will translate into RESOURCE_SEMAPHORE waits.
IsMemoryGrantFeedbackAdjusted: Current state of Adaptive Memory Grant Feedback, that will be represented by one of the following values: NoFirstExecution, YesAdjusting, YesStable, NoAccurateGrant, NoFeedbackDisabled. For more details, check this post about Adaptive Memory Grant Feedback.
LastRequestedMemory: Indicates RequestedMemory value from the previous execution. While trying to calculate the ideal memory grant, Adaptive Memory Grant Feedback will use the requested value from an execution in order to improve the following. This property will allow us to easily check how the grant is being adapted.
MaxQueryMemory: Maximum memory allowed for a single query, according to Resource Governor’s MAX_MEMORY_PERCENT configuration, which by default is 25% of total query memory. In a case where operators are spilling data into tempdb and estimates are fairly correct, we might be experiencing memory starvation.
MaxUsedMemory: Amount of memory actually used by the query during execution.
RequestedMemory: Amount of memory requested, based on RequiredMemory, DesiredMemory, SerialRequiredMemory, SerialDesiredMemory, and MaxQueryMemory. 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 by adjusting parameters as Degree Of Parallelism (DOP), until it fits in.
RequiredMemory: This property represents the required memory to create the respective internal data structures, for a given DOP, when the query runs in parallel. If the query is running in serial mode, RequiredMemory value will be the same as SerialRequiredMemory. It’s mandatory that this amount of memory is available, in order to the query to begin execution.
SerialDesiredMemory: Amount of memory estimated to be needed to run the execution plan on serial mode, without spilling to disk.
SerialRequiredMemory: Required memory for a serial query plan to execute. It’s mandatory that this amount of memory is available in order to the query to begin execution.
And that’s it.