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

December 1, 2019

Question: What do the IsMemoryGrantFeedbackAdjusted states mean? They describe whether memory grant feedback adjusted the statement’s grant, is still learning, or did not make an adjustment.

An adjustable belt illustrates finding a stable fit

I made up this interview question myself after a week of writing about memory grant feedback. It wasn’t an interview someone reported to me. The useful task is to open an actual execution plan, select its root SELECT operator, open Properties, expand MemoryGrantInfo and inspect IsMemoryGrantFeedbackAdjusted.

Original SSMS SELECToperator Properties window points to MemoryGrantInfo — The historical capture shows where MemoryGrantInfo lives. Its clipped SQL is not being offered as a runnable query.

Displayed state	What it tells you
No: First Execution	The first compilation and execution have not used feedback to adjust the grant.
No: Accurate Grant	No spill occurred and at least half of the granted memory was used, so adjustment was not triggered.
No: Feedback disabled	Repeated increases and decreases made the feedback unstable, so adjustment stopped for the statement.
Yes: Adjusting	Feedback was applied; a later execution may adjust it again.
Yes: Stable	The previous and current grants are the same after feedback. This does not prove a globally optimal plan.

Those are the five states discussed in the original SQL Server 2019 article. Current versions also expose Yes: Percentile Adjusting for the percentile algorithm introduced with SQL Server 2022. That mode uses a longer history, with Query Store persistence requirements, rather than only the last execution.

The original compact names omitted spaces and punctuation. Match the displayed value in your SSMS version, and read RequestedMemory, GrantedMemory and MaxUsedMemory alongside it. A stable grant doesn’t say every other aspect of the query is fast.

Feedback can stop when repeated increases and decreases make the grant unstable. Do not assume a universal count of exactly 32 adjustments across versions and algorithms.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.

Discover more from SQL Authority with Pinal Dave

Subscribe to get the latest posts sent to your email.

SQL Memory, SQL Server

How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252

What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254

Using Stored Procedure in SELECT – SQL in Sixty Seconds #193

February 15, 2022

SQL SERVER – SSMS 2012 Reset Keyboard Shortcuts to Default

November 5, 2011

SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

April 3, 2014

Who We Are

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 24 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 19 books and 125 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Shaivi Pinalkumar Dave is a book author and a women’s artistic gymnast.