SQL Server's Memory Grant Feedback --> The Fix That Breaks Things

What Is Memory Grant Feedback?

Before SQL Server runs a query, it estimates how much memory it needs for sorting and joining. But what if it gets it wrong?

• Too little memory → Spills to tempdb (slow)
• Too much memory → Starves other queries

SQL Server 2017+ tries to fix bad estimates based on previous calls with 'Memory Grant Feedback'.  Kinda like:  'Last time I gave you 2GB but you only used 50MB. Next time I'm giving you less.'

The Problem - Memory grant feedback adjusts based on the LAST execution. But what if your query returns different amounts of data each time?

-- Monday: small customer (10 orders)

EXEC usp_GetOrdersByCustomer @CustomerID = 12345

-- SQL grants 100MB, only uses 1MB

-- SQL learns: 'This query needs less memory'

-- Tuesday: huge customer (100,000 orders)  

EXEC usp_GetOrdersByCustomer @CustomerID = 1

-- SQL grants 5MB (based on Monday's run)

-- Spills everywhere, runs for 20 minutes

-- SQL learns: 'This query needs WAY more memory'

-- Wednesday: normal customer (1,000 orders)

EXEC usp_GetOrdersByCustomer @CustomerID = 999

-- SQL grants 5GB (based on Tuesday's disaster)

-- Wastes memory, blocks other queries

How to Tell If You're Affected?

Symptom 1:  Same query, wildly different performance

-- Check memory grant variance

SELECT TOP 10

    q.query_id,

    MIN(rs.avg_memory_grant_kb) AS min_memory_kb,

    MAX(rs.avg_memory_grant_kb) AS max_memory_kb,

    MAX(rs.avg_memory_grant_kb) / NULLIF(MIN(rs.avg_memory_grant_kb),0) AS variance_ratio

FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p 

  ON rs.plan_id = p.plan_id JOIN sys.query_store_query q 

    ON p.query_id = q.query_id

WHERE rs.avg_memory_grant_kb > 0

GROUP BY q.query_id

HAVING COUNT(DISTINCT rs.avg_memory_grant_kb) > 1

ORDER BY variance_ratio DESC

Symptom 2: Spills that appear and disappear -- Today: No spills -- Tomorrow: Massive tempdb spills -- Day after: No spills again -- You haven't changed anything

The Fix: Nuclear Option - Turn It Off Database-Wide

-- Disable both types of memory grant feedback

ALTER DATABASE YourDatabase

SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

ALTER DATABASE YourDatabase

SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Surgical Option - Fix specific queries

-- Add this hint to problematic queries (my preference)

SELECT * FROM YourTable

OPTION (USE HINT('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'))

Smart Option - Keep it, but limit the impact

-- Cap how much memory any query can take

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER WORKLOAD GROUP [default]

WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);

Real Example That Breaks. Here's a simple repro:

-- Create a proc that returns variable rows

CREATE PROCEDURE dbo.usp_GetOrders (

@Days INT

)

AS

SET NOCOUNT ON;

SELECT * FROM Orders

WHERE OrderDate > DATEADD(DAY, -@Days, GETDATE())

-- Run 1: Get 1 day of orders (small)

EXEC dbo.usp_GetOrders @Days = 1

-- Run 2: Get 365 days (huge) - will spill

EXEC dbo.usp_GetOrders @Days = 365

-- Run 3: Get 30 days (medium) - over-allocated

EXEC dbo.usp_GetOrders @Days = 30

Each execution 'learns' from the previous one and often times makes the wrong choice

for the next.

The Bottom Line Memory grant feedback works great for queries that return consistent result sizes.

For everything else, it's like having a thermostat that sets tomorrow's temperature

based on today's weather. If your SQL Server 2017+ queries are randomly slow, randomly fast, or showing

tempdb spills that come and go - this may be why.

More to read: From Brent Ozar

  Memory Grant Feedbacl=k