SQL Server Error 701 - Causes & Fixes

Error 701 occurs when the SQL Server memory manager cannot satisfy a memory allocation request during query execution. The query processor requests memory from the buffer pool manager for operations like sorts, hash joins, and table scans. When available memory falls below the threshold needed for the operation, SQL Server terminates the query rather than risk system instability.

The memory allocation failure typically happens in three scenarios. First, the buffer pool has insufficient free pages after accounting for data cache, procedure cache, and lock manager overhead. Second, a single query requests an excessive memory grant that exceeds available workspace memory, particularly common with parallel operations in SQL Server 2016 and earlier where memory grant feedback was limited. Third, external memory pressure from the operating system or other applications forces SQL Server to shrink its committed memory below operational requirements.

SQL Server 2017 introduced adaptive memory grant feedback that reduces oversized memory grants in subsequent executions, significantly decreasing Error 701 frequency. SQL Server 2019 enhanced this with batch mode memory grant feedback and row mode feedback. SQL Server 2022 added intelligent query processing features that better estimate memory requirements upfront, though Error 701 can still occur under severe memory constraints.

Prior to SQL Server 2012, the memory manager maintained separate allocators based on request size. Allocations of 8KB or less used the single-page allocator, while larger requests used the multi-page allocator. SQL Server 2012 eliminated this distinction by introducing an "any size" page allocator that handles all allocation sizes through a unified memory manager. When the allocator cannot fulfill a request, Error 701 is raised. The Resource Semaphore controls query memory grants, and when the semaphore cannot grant sufficient memory within the timeout period, queries fail with this error.

-- Check current memory pressure and available memory
SELECT 
    total_physical_memory_kb / 1024 AS total_physical_memory_mb,
    available_physical_memory_kb / 1024 AS available_physical_memory_mb,
    system_memory_state_desc,
    sql_memory_model_desc
FROM sys.dm_os_sys_memory;

-- Identify queries waiting for memory grants
SELECT 
    r.session_id,
    r.wait_time,
    r.wait_type,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.query_cost,
    t.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';

-- Review memory grant history and identify problematic queries
SELECT TOP 20
    qs.sql_handle,
    qs.plan_handle,
    qs.max_grant_kb,
    qs.min_grant_kb,
    qs.total_grant_kb / qs.execution_count AS avg_grant_kb,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.max_grant_kb > 100000  -- Focus on queries with large grants
ORDER BY qs.max_grant_kb DESC;

-- Check buffer pool pressure and memory clerk usage
SELECT 
    type,
    name,
    memory_node_id,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 1024
ORDER BY pages_kb DESC;

-- Monitor memory configuration and current allocation
SELECT 
    name,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)')
UNION ALL
SELECT 
    'Current SQL Server Memory (MB)' AS name,
    committed_kb / 1024 AS value_in_use,
    'Currently allocated by SQL Server' AS description
FROM sys.dm_os_sys_info;

Immediate Relief for Active Memory Pressure Clears procedure cache and reduces immediate memory consumption. Only use during active Error 701 incidents.

-- WARNING: Test in development first. This clears all cached plans.
-- Run only during active memory crisis
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
-- Optionally shrink specific databases if tempdb is consuming excessive space
-- DBCC SHRINKDATABASE('tempdb', 10);

Expected impact: Immediate memory relief but temporary performance degradation as plans recompile.

Configure Maximum Server Memory Sets appropriate memory limits based on system resources. Prevents SQL Server from consuming all available memory.

-- Calculate recommended max server memory
-- Reserve 4GB for OS + 1GB per 16GB total RAM above 16GB
DECLARE @TotalMemoryMB INT, @RecommendedMaxMB INT;

SELECT @TotalMemoryMB = total_physical_memory_kb / 1024 
FROM sys.dm_os_sys_memory;

SET @RecommendedMaxMB = @TotalMemoryMB - 4096 - ((@TotalMemoryMB - 16384) / 16 * 1024);
SET @RecommendedMaxMB = CASE WHEN @RecommendedMaxMB < 512 THEN 512 ELSE @RecommendedMaxMB END;

PRINT 'Recommended max server memory: ' + CAST(@RecommendedMaxMB AS VARCHAR(10)) + ' MB';
PRINT 'Current setting: ' + CAST((SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)') AS VARCHAR(10)) + ' MB';

-- Uncomment to apply the change
-- EXEC sp_configure 'max server memory (MB)', @RecommendedMaxMB;
-- RECONFIGURE;

Expected impact: Prevents future memory over-allocation. Requires restart for optimal effect.

Identify and Kill Memory-Intensive Sessions Terminates sessions with excessive memory grants that may be causing system-wide memory pressure.

-- Find sessions with large memory grants and provide kill commands
-- Review carefully before executing
SELECT 
    'KILL ' + CAST(mg.session_id AS VARCHAR(10)) + ';' AS kill_command,
    mg.session_id,
    mg.granted_memory_kb / 1024 AS granted_memory_mb,
    mg.query_cost,
    r.wait_time,
    DB_NAME(r.database_id) AS database_name,
    t.text
FROM sys.dm_exec_query_memory_grants mg
INNER JOIN sys.dm_exec_requests r ON mg.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE mg.granted_memory_kb > 2097152  -- Sessions using more than 2GB
ORDER BY mg.granted_memory_kb DESC;

Expected impact: Immediate memory recovery but kills active user sessions. Use only in crisis situations.

Enable Query Store for Memory Grant Feedback Activates query performance tracking to prevent future memory grant issues through adaptive feedback.

-- Enable Query Store with focus on memory grant tracking
-- Safe to run in production
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON 
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

-- Enable wait statistics collection for resource semaphore waits
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);

Expected impact: Enables automatic memory grant correction for repeat executions. No immediate performance impact.

Configure max server memory to leave adequate space for the operating system and other applications. Reserve 4GB for OS on systems with 16GB or less, plus 1GB for every additional 16GB of RAM. Set min server memory to 1024MB to prevent excessive memory deallocations during idle periods.

Monitor sys.dm_os_ring_buffers for RING_BUFFER_RESOURCE_MONITOR entries that indicate memory pressure events. Establish alerts when available physical memory drops below 10% or when RESOURCE_SEMAPHORE waits exceed normal thresholds. Query Store provides historical memory grant data for identifying problematic query patterns.

Index optimization reduces memory requirements for query execution. Covering indexes eliminate key lookups that consume additional memory grants. Proper statistics maintenance ensures accurate cardinality estimates, preventing oversized memory allocations. Consider columnstore indexes for analytical workloads to reduce memory pressure through better compression.

Configure Resource Governor workload groups to limit memory consumption for specific application pools. Set REQUEST_MAX_MEMORY_GRANT_PERCENT to cap individual query memory usage. This prevents single queries from monopolizing available workspace memory and causing Error 701 for other operations.

Enable trace flag 2453 on SQL Server 2012 SP2 and later to allow table variables to trigger statement recompilation when enough rows are modified, producing better cardinality estimates for queries that use table variables. Improved cardinality estimates generally translate into more appropriate memory grants and fewer Error 701 incidents caused by skewed row count assumptions.