SQL Server Error 701: there is insufficient system memory to run this query

Error 701 is one of SQL Server’s bluntest messages: “There is insufficient system memory in resource pool ‘default’ to run this query.” When it fires, the engine could not satisfy an allocation. Queries that were running fine seconds ago start failing, and the failure cascades into application timeouts, retry storms, and a flood of related errors (17890, 8645).

The error itself tells you very little. It does not say whether the buffer pool is starved, whether a single query is hoarding a memory grant, whether an Extended Events ring buffer has eaten 50 GB, or whether the OS is reclaiming memory from a VM balloon driver. Investigate by source.

What this means

Error 701 is MSSQLSERVER_701. It fires when the SQLOS memory manager cannot satisfy a memory allocation request from any source within the engine. The “resource pool” in the message refers to a Resource Governor pool (the default is default); the error is the same mechanism as Error 8657, which fires when Resource Governor explicitly denies a grant.

Three sources of memory pressure produce 701, matching Microsoft’s standard taxonomy:

1. External pressure - the OS, a co-tenant VM, or another process on the host is squeezing SQL Server. sys.dm_os_sys_memory.system_memory_state_desc reads Available physical memory is low.
2. Internal pressure from DLLs loaded into the SQL Server process - linked servers, CLR assemblies, extended stored procedures. These allocations are not visible as buffer pool usage; they show up as the gap between Process:Private Bytes and SQL Server:Memory Manager\Total Server Memory (KB).
3. Internal pressure from engine components - plan cache bloat, runaway memory grants, USERSTORE_SXC growth from large RPC batches, unbounded Extended Events ring buffers, or in-memory OLTP. These all surface as specific clerks in sys.dm_os_memory_clerks.

A common companion signal is RESOURCE_SEMAPHORE waits in sys.dm_os_wait_stats, plus queries sitting in sys.dm_exec_query_memory_grants with grant_time IS NULL. Do not confuse 701 with Error 9002 (transaction log full) or Error 1105 (filegroup full); those are disk space failures, not memory failures.

flowchart TD
    A[Error 701 fires] --> B{sys.dm_os_sys_memory low?}
    B -- Yes --> C[External OS pressure]
    B -- No --> D{Top clerk in dm_os_memory_clerks?}
    D -- SQLBUFFERPOOL --> E[Buffer pool exhausted]
    D -- SQLCP / OBJCP --> F[Plan cache bloat]
    D -- MEMORYCLERK_XE --> G[XE ring buffer leak]
    D -- USERSTORE_SXC --> H[Large RPC batch]
    D -- Other --> I[CLR / linked server / XP]
    C --> J[Find competing process or raise max server memory]
    E --> K[Check dm_exec_query_memory_grants]
    F --> L[Parameterize or flush plan cache]
    G --> M[Cap XE ring buffer]
    H --> N[Batch the RPC call]
    I --> O[Audit loaded modules]

Common causes

Quick checks

All read-only. Run them in order; the first three usually localize the problem in under a minute.

-- 1. OS-level memory state
SELECT total_physical_memory_kb / 1024 AS total_mb,
       available_physical_memory_kb / 1024 AS available_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- 2. SQL Server process memory and OS pressure flag
SELECT physical_memory_in_use_kb / 1024 AS sql_physical_mb,
       locked_page_allocations_kb / 1024 AS locked_mb,
       process_physical_memory_low,
       process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- 3. Top memory clerks by pages_kb
SELECT TOP (15) type, name, pages_kb / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;

-- 4. Currently waiting and granted memory grants
SELECT session_id, request_time, grant_time,
       requested_memory_kb, granted_memory_kb,
       used_memory_kb, wait_time_ms, dop, query_cost
FROM sys.dm_exec_query_memory_grants
ORDER BY COALESCE(wait_time_ms, 0) DESC,
         granted_memory_kb DESC;

-- 5. Memory Grants Pending counter (point-in-time)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
  AND object_name LIKE '%Memory Manager%';

-- 6. RESOURCE_SEMAPHORE waits (cumulative; snapshot twice for delta)
SELECT waiting_tasks_count, wait_time_ms,
       max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE';

-- 7. Top single-use ad-hoc plan waste
SELECT COUNT(*) AS single_use_plans,
       SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS wasted_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc';

-- 8. Resource Governor workload group memory caps
SELECT name, request_max_memory_grant_percent,
       request_memory_grant_timeout_sec, max_dop
FROM sys.dm_resource_governor_workload_groups;

-- 9. Extended Events sessions and their targets
SELECT s.name, s.total_buffer_size / 1024 AS buffer_kb,
       t.target_name, CAST(t.target_data AS XML) AS target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
  ON t.event_session_address = s.address
WHERE s.name <> 'system_health';

-- 10. Recent 701 entries in the error log
EXEC sp_readerrorlog 0, 1, 'Error: 701';

How to diagnose it

1. Establish whether the pressure is external or internal. Look at sys.dm_os_sys_memory.system_memory_state_desc. If it reads Available physical memory is low, the OS itself is squeezed; this is external pressure. If it reads Available physical memory is high, the pressure is internal to the SQL Server process.
2. Confirm the OS is not paging SQL Server out. Check process_physical_memory_low in sys.dm_os_process_memory. A value of 1 means Windows told SQL Server to shrink its working set. Without Lock Pages in Memory (LPIM), this can be catastrophic and invisible in buffer pool counters. On Linux, the equivalent concern is the OOM killer; check dmesg -T | grep -i oom for OOM activity.
3. Read the top memory clerks. Sort sys.dm_os_memory_clerks by pages_kb descending. The expected dominant consumer is MEMORYCLERK_SQLBUFFERPOOL. If something else is at the top, or if CACHESTORE_SQLCP (plan cache for ad-hoc SQL) is more than 10-15 percent of max server memory, you have found the leak.
4. If the buffer pool dominates and is at max server memory, the issue is that max server memory is set too low for the workload, or queries are evicting pages through legitimate (but large) scans. Cross-check PLE, PAGEIOLATCH_* waits, and Total Server Memory (KB) vs Target Server Memory (KB) to confirm a memory pressure spiral.
5. Check active memory grants. Queries in sys.dm_exec_query_memory_grants with grant_time IS NULL are waiting; queries with very large granted_memory_kb are hoarding. A single bad plan from parameter sniffing can take a 25 percent grant on the default workload group and starve everyone else.
6. If RESOURCE_SEMAPHORE dominates waits, this is a memory grant problem, not a buffer pool problem. The two are separate pools within the engine.
7. Inspect Extended Events sessions. MEMORYCLERK_XE growing steadily over hours is a classic symptom of a user-defined XE session with an oversized ring buffer target.
8. Check Resource Governor. If the 701 message names a non-default resource pool, the workload group’s REQUEST_MAX_MEMORY_GRANT_PERCENT may be set too low. The default is 25 percent of the pool; values close to zero will reject almost any grant-requiring query.
9. Correlate with deployment context. On a VM, ask whether memory was overcommitted or whether a balloon driver is active. On a host shared with other instances, check whether a co-tenant was running an index rebuild.

Metrics and signals to monitor

Fixes

Genuine buffer pool exhaustion

If the buffer pool is at max server memory and the OS is not under pressure, the most common fix is to raise max server memory. Leave 4-8 GB (or roughly 10-20 percent of total RAM, whichever is larger) for the OS and any co-located services. See SQL Server max server memory: setting it so the OS and buffer pool both survive for the full method.

If the OS is squeezed by a co-tenant, you must move or throttle the co-tenant. Raising max server memory on a host with no free RAM will worsen 701, not fix it.

Runaway memory grant

Identify the query in sys.dm_exec_query_memory_grants and KILL <session_id> for immediate relief.

Warning: KILL rolls back the current transaction. If the query has already modified rows, rollback duration depends on the amount of work to undo.

The underlying cause is almost always a bad plan from stale statistics or parameter sniffing:

• Update statistics on the affected tables.
• Force a known-good plan in Query Store (sp_query_store_force_plan).
• Reduce the per-query grant ceiling with Resource Governor (REQUEST_MAX_MEMORY_GRANT_PERCENT below the default 25 percent).
• On SQL Server 2019 and later (database compatibility level 150), confirm that row mode memory grant feedback is enabled for the database; it will correct repeated over-grants. SQL Server 2022 persists grant feedback through Query Store.

See SQL Server Memory Grants Pending above zero: queries queued before they can run for an extended walkthrough.

Plan cache bloat from ad-hoc SQL

Enable optimize for ad hoc workloads at the server level to cache only a stub on first execution. Parameterize the application SQL, or enable forced parameterization on databases where the workload is dominated by non-parameterized ad-hoc queries.

Warning: DBCC FREEPROCCACHE clears the entire plan cache. Every cached plan is discarded and the next round of executions will recompile, causing a CPU spike and elevated compilations per second. Use it only as an emergency measure, not on a regular schedule.

See SQL Server high compilations per second: plan cache pollution and CPU burn for the plan-cache angle.

External DLL pressure (CLR, linked servers, XPs)

This is the trickiest case because the consumption is not visible as a normal clerk. Compare Process:Private Bytes against SQL Server:Memory Manager\Total Server Memory (KB); if Private Bytes is much higher, the gap is consumed by loaded DLLs. Common culprits are linked server providers (especially OLE DB providers that allocate large buffers), unverified CLR assemblies, and third-party extended stored procedures. Audit sys.dm_clr_appdomains, sys.servers for linked server activity, and xp_cmdshell enablement. The long-term fix is to remove the offending module or move the workload to a separate host.

Unbounded Extended Events ring buffer

Find the offending session in sys.dm_xe_sessions (look at buffer memory usage) and reconfigure its ring buffer target with an explicit MAX_MEMORY, or switch to an event_file target with rollover. Stopping the session immediately releases the memory.

USERSTORE_SXC growth from large RPC batches

This pattern surfaces as a large USERSTORE_SXC clerk. It is associated with large RPC batches that pass many sql_variant parameters. Reduce the batch size on the client side, or change the parameter types so they are not all sql_variant. The fix is in the application, not in the engine.

Resource Governor pool misconfiguration

If the 701 message names a non-default resource pool, inspect sys.dm_resource_governor_workload_groups. A workload group with REQUEST_MAX_MEMORY_GRANT_PERCENT near zero will reject almost every grant-requiring query. Reconfigure with ALTER WORKLOAD GROUP and run ALTER RESOURCE GOVERNOR RECONFIGURE.

Warning: Resource Governor configuration changes are live and affect every connection classified into the group immediately.

VM memory overcommit

If process_physical_memory_low is 1 and the SQL internal clerks look normal, the host is overcommitted. Work with the virtualization team to reserve memory for the SQL Server VM, disable balloon driver reclamation for the SQL VM, or move co-tenants. This is not fixable from inside SQL Server.

Prevention

• Set max server memory correctly. This is the single most important prevention step on a dedicated host. Leaving it at the default lets SQL Server consume memory until the OS thrashes.
• Enable optimize for ad hoc workloads on servers running any significant ad-hoc SQL workload. The cost is one extra compilation per ad-hoc query on first run; the benefit is lower plan-cache memory waste.
• Cap user-defined Extended Events sessions. Every user XE session should have an explicit MAX_MEMORY, or use event_file with rollover.
• Review Resource Governor workload groups before promotion. REQUEST_MAX_MEMORY_GRANT_PERCENT = 0 looks safe but rejects almost everything that needs a grant.
• Reserve VM memory. Do not overcommit hosts running SQL Server VMs. Configure LPIM on Windows, or the equivalent mssql OOM-score adjustment on Linux.
• Alert on Memory Grants Pending. It is zero most of the time; any sustained nonzero value means grant pool contention is starting.
• Snapshot sys.dm_os_memory_clerks periodically. A clerk that grows monotonically over days is a leak; catching it early prevents the next incident.

How Netdata helps

• Per-second memory clerk visibility shows which clerk is growing while 701 is firing, without running a manual DMV sweep under load.
• Correlate Memory Grants Pending, RESOURCE_SEMAPHORE waits, and buffer pool size on a single timeline to distinguish grant exhaustion from buffer pool exhaustion.
• OS-side memory metrics on the same dashboard make it obvious whether the pressure is external or internal to SQL Server.
• Error log ingestion surfaces 701, 17890, and 8645 as they occur, giving a timeline of when memory pressure started.
• Per-NUMA-node breakdowns catch the case where one node is starved while the aggregate looks healthy.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators
• SQL Server log autogrow stall: why every write pauses while the log file grows
• SQL Server log backups missing: the full-recovery log that grows forever
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• SQL Server max server memory: setting it so the OS and buffer pool both survive
• SQL Server Memory Grants Pending above zero: queries queued before they can run