SQL Server Error 8645 - Causes & Fixes

Error 8645 triggers when the Resource Semaphore, SQL Server's internal memory gatekeeper for query execution, cannot fulfill memory grant requests within the configured timeout period. The Resource Semaphore allocates memory from the buffer pool for operations like sorts, hash joins, bulk operations, and index builds.

When a query requests memory, the Query Memory Manager calculates the required grant based on cardinality estimates and operation complexity. If insufficient memory exists in the query execution memory pool (controlled by the "memory for query optimization" configuration), the request enters a RESOURCE_SEMAPHORE wait state. The error fires when this wait exceeds the query timeout threshold.

SQL Server 2016 introduced significant improvements to memory grant feedback, where the engine adjusts future grants based on actual memory usage patterns. SQL Server 2019 added batch mode memory grant feedback and row mode memory grant feedback persistence. SQL Server 2022 enhanced this with parameter-sensitive plan optimization, reducing memory grant estimation errors.

The memory pressure originates from several sources: oversized memory grants from poor cardinality estimates, parallel queries consuming multiple grants simultaneously, memory-intensive operations running concurrently, or insufficient max server memory configuration. Large memory grants can monopolize the available pool, starving smaller queries even when total system memory remains available.

-- Check current memory grants and waits
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    t.text as query_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_query_memory_grants mg ON r.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'RESOURCE_SEMAPHORE'
ORDER BY r.wait_time DESC;

-- Analyze Resource Semaphore memory pressure
SELECT 
    pool_id,
    max_memory_kb / 1024 as max_memory_mb,
    used_memory_kb / 1024 as used_memory_mb,
    target_memory_kb / 1024 as target_memory_mb,
    outstanding_memory_grants_count,
    max_target_memory_kb / 1024 as max_target_memory_mb
FROM sys.dm_exec_query_resource_semaphores
WHERE pool_id = 0; -- Default resource pool

-- Identify queries with excessive memory grants
SELECT TOP 10
    qs.sql_handle,
    qs.plan_handle,
    qs.max_grant_kb / 1024 as max_grant_mb,
    qs.min_grant_kb / 1024 as min_grant_mb,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count as avg_duration_ms,
    t.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE qs.max_grant_kb > 102400 -- Queries using more than 100MB
ORDER BY qs.max_grant_kb DESC;

-- Check memory configuration and usage
SELECT 
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Target Server Memory (KB)',
    'Total Server Memory (KB)',
    'Memory Grants Outstanding',
    'Memory Grants Pending'
)
AND object_name LIKE '%Memory Manager%';

-- Historical memory grant timeouts from error log
EXEC xp_readerrorlog 0, 1, N'8645', NULL, NULL, NULL, N'DESC';

Use Resource Governor to Limit Memory Grants

-- Create a resource pool to cap memory grants for heavy workloads
-- This prevents runaway queries from starving other sessions
CREATE RESOURCE POOL LimitedMemoryPool
WITH (
    MAX_MEMORY_PERCENT = 40,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25
);

CREATE WORKLOAD GROUP LimitedMemoryGroup
USING LimitedMemoryPool;

ALTER RESOURCE GOVERNOR RECONFIGURE;

-- If max server memory is too low, increase it (requires sufficient physical RAM)
-- Check current setting:
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name = 'max server memory (MB)';

-- Increase if the server has available RAM headroom:
-- EXEC sp_configure 'max server memory (MB)', <new_value>;
-- RECONFIGURE;

Identify the specific queries causing excessive memory grant requests and optimize them (update statistics, rewrite queries, add indexes) rather than changing global memory settings.

Kill Blocking Memory Grant Sessions

-- Identify and terminate sessions holding excessive memory grants
DECLARE @SessionID INT;
DECLARE kill_cursor CURSOR FOR
SELECT r.session_id
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_query_memory_grants mg ON r.session_id = mg.session_id
WHERE r.wait_type = 'RESOURCE_SEMAPHORE'
  AND r.wait_time > 300000 -- 5 minutes
  AND mg.granted_memory_kb > 1048576; -- 1GB

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @SessionID;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session ' + CAST(@SessionID AS VARCHAR(10));
    EXEC('KILL ' + @SessionID);
    FETCH NEXT FROM kill_cursor INTO @SessionID;
END;

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Production impact: Terminates user sessions. Verify sessions are not critical business processes before execution.

Force Plan Recompilation for Problem Queries

-- Clear plan cache for queries with excessive memory grants
SELECT DISTINCT 
    'DBCC FREEPROCCACHE (' + QUOTENAME(CONVERT(VARBINARY(64), plan_handle), '''') + ');' as clear_command
FROM sys.dm_exec_query_stats 
WHERE max_grant_kb > 512000 -- 500MB threshold
  AND execution_count > 10;

Removes execution plans from cache, forcing recompilation with potentially better memory estimates. Monitor CPU usage after execution.

Implement Resource Governor

-- Create resource pool for memory-intensive queries
CREATE RESOURCE POOL MemoryIntensivePool
WITH (
    MAX_MEMORY_PERCENT = 30,
    MAX_CPU_PERCENT = 40
);

CREATE WORKLOAD GROUP MemoryIntensiveGroup
USING MemoryIntensivePool;

-- Create classifier function (customize based on your application)
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME = 'default';
    
    IF (SUSER_NAME() = 'DOMAIN\ReportingUser' 
        OR APP_NAME() LIKE '%SSRS%'
        OR APP_NAME() LIKE '%PowerBI%')
        SET @WorkloadGroup = 'MemoryIntensiveGroup';
    
    RETURN @WorkloadGroup;
END;

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Configure max server memory to leave adequate RAM for the operating system and other applications, typically 10-15% of total RAM. Monitor Resource Semaphore waits through sys.dm_os_wait_stats and establish baseline thresholds for memory grant queue length.

Implement memory grant feedback by enabling compatibility level 140+ on SQL Server 2017 and higher. This allows the engine to learn from actual memory usage and adjust future grants automatically. Review execution plans for memory-intensive operations and consider query rewrites to reduce memory footprint.

Establish Resource Governor policies to limit memory consumption by specific applications or user groups. Create separate resource pools for reporting workloads that typically require large memory grants. Set appropriate MAX_MEMORY_PERCENT values to prevent runaway queries from consuming all available memory.

Use columnstore indexes for analytical workloads to reduce memory requirements for aggregations and sorts. Partition large tables to enable parallel operations with smaller per-thread memory grants. Schedule memory-intensive maintenance operations during off-peak hours to avoid contention with user queries.

Monitor memory grant patterns through Extended Events, specifically the sqlserver.query_memory_grant_usage event. Set up alerts when memory grant wait times exceed acceptable thresholds. Regularly review and update statistics to improve cardinality estimates, which directly impact memory grant accuracy.