Question: What are Different Status of IsMemoryGrantFeedbackAdjusted (MemoryGrantInfo Property)?
Answer: This question is actually made up by me and was never asked in an interview. The source of the question is all the blog posts that I have been writing this week on the topic of Memory Grant Feedback. Today we will see a consolidated answer to this question which I have discussed across multiple of the blog posts.
Here are the five states of the IsMemoryGrantFeedbackAdjusted property which is the sub-property of the MemoryGrantInfo. 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. Now pay attention to the property – IsMemoryGrantFeedbackAdjusted.
Now let us discuss various states of IsMemoryGrantFeedbackAdjusted.
NoFirstExecution: When you execute the stored procedure first time you will find the following details in the column IsMemoryGrantFeedbackAdjusted – NoFirstExecution. That means, the query has run the first time and there is no history available for adjusting the memory.
YesAdjusting:Â This means the memory grant feedback is now taking feedback from the earlier execution and adjusting it for the next execution.
YesStable:Â This means the memory grant has found the most optimal value and has applied to the query.
NoAccurateGrant: This means there was no need for an additional memory grant and there was no spill on the disk.
NoFeedbackDisabled: This happens when SQL Server Engine is not able to figure out what is the best possible memory grant for the query and stop adjusting the memory grant after a few attempts (32 to be precise).
If you want to learn more about Memory Grant Feedback, here are a few additional blog posts where you can learn more about this topic.
- SQL SERVER – Introduction to Memory Grant Feedback
- SQL SERVER – Memory Grant Feedback – MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted
- SQL SERVER – Memory Grant Feedback – No Feedback Disabled
- SQL SERVER – Disable Memory Grant Feedback at Database Level and Query Level
You can follow me on twitter for the latest updates.
Reference: Pinal Dave (https://blog.sqlauthority.com)
