Detecting When Memory Grant Feedback or Batch Mode Actually Apply (or Don’t)

This post continues from Part 1. Here, we look at how to detect when SQL Server actually applies — or suspends — two of the most impactful Intelligent Query Processing (IQP) features:

• Memory Grant Feedback (MGF)
• Batch Mode on Rowstore (BMoR)

These detection techniques use only official SQL Server attributes, DMVs, and extended events.

DETECTING MEMORY GRANT FEEDBACK ACTIVITY OR SUSPENSION

Memory Grant Feedback was introduced in SQL Server 2019 and enhanced in SQL Server 2022+. Microsoft documents several plan attributes that reveal how the engine adjusted or suspended feedback. These attributes appear under the MemoryGrantInfo node in the execution plan.

Use this query to inspect recent cached plans:

SELECT 
    qs.last_execution_time,
    qp.query_plan.value('(//MemoryGrantInfo/@IsMemoryGrantFeedbackAdjusted)[1]', 'varchar(50)') AS FeedbackAdjusted,
    qp.query_plan.value('(//MemoryGrantInfo/@LastRequestedMemory)[1]', 'int') AS LastRequestedMemoryKB
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//MemoryGrantInfo') = 1;

Meaning of values (based on Microsoft documentation):

• Yes: Stable — SQL Server applied feedback and stabilized the memory grant.
• Yes: Adjusting / PercentileAdjusting — feedback is still tuning the grant.
• No: Feedback disabled — SQL Server suspended MGF after repeated regressions or oscillations.
• No: FirstExecution / AccurateGrant — initial run or grant deemed already appropriate.

You can also monitor MGF with the lightweight extended event:
memory_grant_updated_by_feedback
It fires each time SQL Server updates a memory grant using feedback.

DETECTING BATCH MODE ON ROWSTORE (BMOR)

Batch Mode on Rowstore is available at database compatibility level 150 or higher. However, SQL Server does not guarantee that batch mode will be chosen — it depends entirely on operator eligibility and plan shape. When batch mode is selected, the execution plan exposes the attribute:

• ActualExecutionMode="Batch"

If you see ActualExecutionMode="Row" for all operators, SQL Server chose not to apply batch mode for that plan. This determination is documented in Microsoft’s IQP operator behavior reference.

WHY PLAN INSPECTION MATTERS

IQP features are adaptive. They can:

• Apply only after multiple executions
• Adjust their behavior over time
• Suspend feedback when it proves unstable
• Choose batch mode only when supported by the operator pipeline

All of this is visible only by inspecting:

• The MemoryGrantInfo attributes
• ActualExecutionMode in the execution plan
• DMVs (ie., sys.dm_exec_query_stats)
• Extended events

This makes plan analysis essential for understanding real query performance behavior.

More to Read:

• Memory Grant Feedback (batch + row mode)
• Intelligent Query Processing (overview & feature matrix)
• Intelligent Query Processing Details (includes Batch Mode on Rowstore)

I will follow up with a Part 3, where we will walk through real-world examples showing:

• Queries where Memory Grant Feedback stabilized
• Queries where MGF disabled itself
• Plans where Batch Mode on Rowstore was selected
• Plans where BMoR did not apply, and why