Memory Grants, Sort/Hash Spills, Grant Feedback, and Concurrency Pressure

Learning outcomes

A query does not need enough memory to hold every input row before it can run. Operators such as sorts and hash joins/aggregates request workspace memory from SQL Server’s query-execution grant infrastructure. If the grant is too small, work can spill to tempdb. If grants are too large, one query may look fast while many neighbors wait on RESOURCE_SEMAPHORE. The correct unit of tuning is therefore the workload, not one plan in isolation.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubWorkspaceLab') IS NULL  CREATE DATABASE ServiceHubWorkspaceLab;GOALTER DATABASE ServiceHubWorkspaceLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubWorkspaceLab;GOIF SCHEMA_ID(N'lab12') IS NULL EXEC(N'CREATE SCHEMA lab12 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab12.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab12.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    payload varchar(400) NULL,    CONSTRAINT PK_lab12_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (60000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab12.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,payload)  SELECT CASE WHEN n <= 30000 THEN 1 ELSE 2 + n % 4999 END,         CASE WHEN n % 3=0 THEN 'N01' WHEN n % 3=1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 19=0 THEN 'ESCALATED' WHEN n % 5=0 THEN 'CLOSED' ELSE 'OPEN' END,         CONVERT(tinyint,1 + n % 5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(10 + (n % 20000)/10.0 AS decimal(12,2)),         CASE WHEN n % 13=0 THEN REPLICATE('x',350) ELSE REPLICATE('x',40) END  FROM n;  CREATE INDEX IX_lab12_RegionStatus    ON lab12.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GO

1. A grant is an execution reservation, not “buffer pool used by the table”

SQL Server estimates how much memory selected operators need. The plan has a minimum required amount and a desired/ideal amount; at execution the request enters a resource semaphore. If enough workspace memory is available, SQL Server grants memory and the query starts. If not, it can wait. A query that performs only narrow seeks may need no grant at all and therefore will not appear in sys.dm_exec_query_memory_grants.

SELECT mg.session_id,mg.request_id,mg.dop,       mg.required_memory_kb,mg.requested_memory_kb,mg.granted_memory_kb,       mg.used_memory_kb,mg.max_used_memory_kb,mg.ideal_memory_kb,       mg.grant_time,mg.wait_time_ms,mg.queue_id,       LEFT(st.text,400) AS batch_textFROM sys.dm_exec_query_memory_grants AS mgCROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS stORDER BY CASE WHEN mg.grant_time IS NULL THEN 0 ELSE 1 END,mg.requested_memory_kb DESC;GOSELECT resource_semaphore_id,target_memory_kb,available_memory_kb,       granted_memory_kb,used_memory_kb,grantee_count,waiter_count,       timeout_error_count,forced_grant_countFROM sys.dm_exec_query_resource_semaphores;GO

A waiting grant usually has grant_time IS NULL; RESOURCE_SEMAPHORE is the common wait family. Do not run a giant DMV query that itself sorts/aggregates huge plan-cache data during a memory crisis—the diagnostic can become part of the problem.

2. Generate a sort/hash workspace requirement and inspect the plan

The following statement is intentionally workspace-heavy relative to simple OLTP seeks. Whether it actually spills depends on available memory, estimates, concurrency, DOP and build. The lab does not claim a spill must occur.

USE ServiceHubWorkspaceLab;GOSET STATISTICS IO ON;SET STATISTICS TIME ON;GOSELECT customer_id,       COUNT_BIG(*) AS order_count,       SUM(amount) AS amount,       MAX(payload) AS representative_payloadFROM lab12.WorkOrderFactGROUP BY customer_idORDER BY amount DESC,customer_id;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;

In an actual plan, inspect the root MemoryGrantInfo and the Sort/Hash operator properties. Compare estimated and actual rows, requested/granted/used memory, spill warnings, spill level/pass information when present, and tempdb reads/writes. A spill is an outcome; the upstream cause may be a cardinality error, skew, a wide row, a plan shape, concurrency, or an intentionally constrained grant.

3. Undergrant and overgrant fail differently

The common wrong approach is to fix a spill by forcing a much larger minimum grant. That can move the incident from “one query spills” to “twenty queries wait.” Hints such as MIN_GRANT_PERCENT and MAX_GRANT_PERCENT exist, but they are interventions that require representative concurrency testing and rollback, not a first-line response.

4. Use Extended Events when the plan snapshot is not enough

Microsoft documents focused events such as hash_spill_details, sort_warning, execution_warning, memory_grant_updated_by_feedback, and feedback-loop events. Create a short-lived event session only when needed, filter to the target database/query where practical, write to an appropriate event file, and remove it after capture.

SELECT o.name AS event_name,c.name AS column_name,c.descriptionFROM sys.dm_xe_objects AS oLEFT JOIN sys.dm_xe_object_columns AS c  ON c.object_package_guid=o.package_guid AND c.object_name=o.nameWHERE o.object_type='event'  AND o.name IN ('hash_spill_details','sort_warning','execution_warning',                 'memory_grant_updated_by_feedback','memory_grant_feedback_loop_disabled')ORDER BY o.name,c.column_type,c.name;GO

Discovery first avoids copying an event definition that changed across versions. Extended Events can expose SQL text and parameter values, so production collection has security and data-handling consequences.

5. Memory grant feedback learns only from eligible repeated executions

Memory grant feedback (MGF) can adjust later grants after observing over- or under-granted executions. SQL Server introduced batch-mode feedback first, row-mode feedback later, and SQL Server 2022 added Query Store persistence and percentile-based behavior. Persistence requires Query Store state/configuration that supports it. A statement with OPTION(RECOMPILE) does not keep a reusable cached plan for feedback in the ordinary way, so recompilation and feedback can work against each other.

USE ServiceHubWorkspaceLab;GOSELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name IN ('BATCH_MODE_MEMORY_GRANT_FEEDBACK',               'ROW_MODE_MEMORY_GRANT_FEEDBACK',               'MEMORY_GRANT_FEEDBACK_PERSISTENCE');SELECT actual_state_desc,desired_state_descFROM sys.database_query_store_options;GO

If Query Store is off in this disposable Chapter 12 database, persistence-specific behavior should not be claimed. You can still observe cache-based feedback for eligible statements. Chapter 11 already taught how to configure Query Store safely; do not enable it merely to make a screenshot prettier.

6. Concurrency test: the missing dimension in many tuning demonstrations

To test grant pressure credibly, run the same representative query from several independent sessions while a monitoring session samples sys.dm_exec_query_memory_grants, resource semaphores, waits and tempdb usage. Increase concurrency gradually. Stop before destabilizing the machine. A local Developer instance on a laptop is useful for mechanism learning but does not justify production thresholds.

Production judgment

Fix the most causal and least invasive problem: wrong row estimates, avoidable width, unnecessary sort/order, poor join shape, stale statistics, or skew before reaching for memory-grant hints. Validate across parameter classes and concurrency. A spill is not automatically a defect if the alternative is reserving excessive memory for rare executions. Likewise, a large grant is not automatically waste if it prevents an expensive spill and the server has capacity.

Authoritative references

• sys.dm_exec_query_memory_grants — active/waiting grants
• sys.dm_exec_query_resource_semaphores — grant pool pressure
• Troubleshoot memory grant issues — RESOURCE_SEMAPHORE, spills and Extended Events
• Memory grant feedback — feedback modes and persistence
• Extended Events quickstart — focused collection and permissions