Introduction SQL Server memory grant feedback adjusts memory allocation for queries based on execution history. When a query's memory needs vary significantly between executions (due to parameter sniffing or data distribution changes), the feedback loop can oscillate between over-granting and under-granting memory, causing alternating spills to tempdb and wasted memory.

Symptoms - `RESOURCE_SEMAPHORE` waits appearing intermittently - Query execution time varying wildly between executions of the same query - `sys.dm_exec_query_memory_grants` shows `granted_memory_kb` fluctuating - Tempdb spill warnings in actual execution plans - Overall server performance degrading as memory grants compete

Common Causes - Parameter sniffing causing dramatically different memory requirements - Data distribution changes making cached memory grants inaccurate - Sort and hash operations with highly variable input sizes - Multiple concurrent queries competing for the same memory pool - Statistics not updated, leading to inaccurate cardinality estimates

Step-by-Step Fix 1. **Check current memory grant status": ```sql -- Queries waiting for memory SELECT session_id, request_time, grant_time, requested_memory_kb, granted_memory_kb, required_memory_kb, wait_time_ms, query_plan FROM sys.dm_exec_query_memory_grants CROSS APPLY sys.dm_exec_query_plan(plan_handle);

-- Resource semaphore waiters SELECT * FROM sys.dm_exec_requests WHERE wait_type = 'RESOURCE_SEMAPHORE'; ```

  1. 1.**Identify queries with memory grant issues":
  2. 2.```sql
  3. 3.SELECT TOP 20
  4. 4.qs.execution_count,
  5. 5.qs.total_grant_kb / qs.execution_count AS avg_grant_kb,
  6. 6.qs.total_used_grant_kb / qs.execution_count AS avg_used_kb,
  7. 7.qs.max_used_grant_kb,
  8. 8.qs.total_spills / qs.execution_count AS avg_spills,
  9. 9.SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
  10. 10.CASE qs.statement_end_offset
  11. 11.WHEN -1 THEN DATALENGTH(st.text)
  12. 12.ELSE qs.statement_end_offset - qs.statement_start_offset
  13. 13.END / 2) AS query_text
  14. 14.FROM sys.dm_exec_query_stats qs
  15. 15.CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  16. 16.WHERE qs.total_spills > 0
  17. 17.ORDER BY qs.total_spills DESC;
  18. 18.`
  19. 19.**Fix parameter sniffing with query hints":
  20. 20.```sql
  21. 21.-- Use OPTIMIZE FOR UNKNOWN to use average statistics
  22. 22.CREATE PROCEDURE GetOrders
  23. 23.@customerId INT
  24. 24.AS
  25. 25.BEGIN
  26. 26.SELECT * FROM orders
  27. 27.WHERE customer_id = @customerId
  28. 28.OPTION (OPTIMIZE FOR UNKNOWN);
  29. 29.END;

-- Or use RECOMPILE for highly variable parameters CREATE PROCEDURE GetOrders @customerId INT AS BEGIN SELECT * FROM orders WHERE customer_id = @customerId OPTION (RECOMPILE); END; ```

  1. 1.**Use Resource Governor to limit memory grants":
  2. 2.```sql
  3. 3.-- Create a resource pool with memory grant cap
  4. 4.CREATE RESOURCE POOL ReportingPool
  5. 5.WITH (
  6. 6.MAX_MEMORY_PERCENT = 30,
  7. 7.MIN_MEMORY_PERCENT = 10
  8. 8.);

-- Create a workload group CREATE WORKLOAD GROUP ReportingGroup WITH ( REQUEST_MAX_MEMORY_GRANT_PERCENT = 25 ) USING ReportingPool;

-- Create classifier function CREATE FUNCTION dbo.ClassifierFunction() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN IF APP_NAME() LIKE '%Reporting%' RETURN 'ReportingGroup'; RETURN 'default'; END; ```

Prevention - Keep statistics updated to ensure accurate cardinality estimates - Use `OPTIMIZE FOR UNKNOWN` or `RECOMPILE` for queries with parameter sensitivity - Monitor `RESOURCE_SEMAPHORE` waits and memory grant spills - Set up Resource Governor for mixed workloads (OLTP + reporting) - Review execution plans for memory grant warnings during code review - Use `QUERY_STORE` to track query plan and memory grant changes over time - Consider partitioning large tables to reduce memory requirements for scans