MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253

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.

MemoryGrantInfo - What are Different Status of IsMemoryGrantFeedbackAdjusted? - Interview Question of the Week #253 MemoryGrantInfo-800x172

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.

Solarwinds

MemoryGrantInfo - What are Different Status of IsMemoryGrantFeedbackAdjusted? - Interview Question of the Week #253 memorygrant

MemoryGrantInfo - What are Different Status of IsMemoryGrantFeedbackAdjusted? - Interview Question of the Week #253 memorygrants1

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.

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

Solarwinds
, ,
Previous Post
How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252
Next Post
What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254

Related Posts

Leave a Reply

Menu