Insufficient System Memory and Memory Grant Timeouts (Errors 701 and 8645)

These two arrive together often enough that they get treated as one symptom with one answer, and the one answer is usually wrong. Adding memory to a server hitting 8645 can change nothing at all, because the memory was already there.

701 and 8645 Are Different Failures

Message text from sys.messages on SQL Server 2025 CU8, not captured on this page.

Microsoft Learn: MSSQLSERVER_701 · MSSQLSERVER_8645

Severity distinguishes them. 701 is severity 19; 8645 is severity 17 and ends a request whose memory-grant wait times out. The 8645 message says “Rerun the query.” That is a possible next step, not a guarantee that the retry will succeed. 701 makes no such offer.

Worth separating from both, because it sits in the same mental bucket and is neither: error 8623, “the query processor ran out of internal resources and could not produce a query plan”, severity 16. That is not memory pressure. It is a statement too complex to compile, nearly always a generated query or an enormous IN list, and no amount of RAM changes it.

Do Not Diagnose This From Cumulative Wait Stats

Every article about 8645 points you to RESOURCE_SEMAPHORE in sys.dm_os_wait_stats. The wait type matters, but that cumulative DMV does not tell you whether a query is waiting now.

This capture is from HPAI01. It separates the cumulative wait total from the current grant queue.

The wait-stat row is cumulative since startup, not a list of queries waiting now. At this capture, there were no pending memory grants and no active requests waiting on RESOURCE_SEMAPHORE. Total wait time is summed across concurrent tasks, so it can exceed uptime; max_wait_time_ms is the longest individual wait.

Run these checks together to compare cumulative waits with the current grant queue:

SET NOCOUNT ON;

DECLARE @up_ms bigint =
    (SELECT DATEDIFF_BIG(ms, sqlserver_start_time, GETDATE())
     FROM sys.dm_os_sys_info);

SELECT wait_type, waiting_tasks_count AS waits, wait_time_ms, max_wait_time_ms,
       CAST(wait_time_ms * 100.0 / NULLIF(@up_ms, 0) AS decimal(10,1)) AS pct_of_uptime
FROM sys.dm_os_wait_stats
WHERE wait_type = N'RESOURCE_SEMAPHORE';

SELECT COUNT_BIG(*) AS pending_memory_grants,
       MAX(wait_time_ms) AS longest_pending_grant_ms
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL;

SELECT COUNT_BIG(*) AS active_resource_semaphore_requests,
       MAX(wait_time) AS longest_active_wait_ms
FROM sys.dm_exec_requests
WHERE wait_type = N'RESOURCE_SEMAPHORE';

The usable ways to measure memory grant pressure are all narrower in time than “since the instance started”:

• Take a delta. Snapshot sys.dm_os_wait_stats, wait a known interval, snapshot again, subtract. A window you chose is interpretable in a way that “since startup” is not.
• Look at what is happening right now, with the two DMVs below. Nothing cumulative, nothing to misread.
• Use Query Store if the database has it on. It keeps per-query memory grant history with timestamps, which is what you actually want when the 8645 happened at 3am.

What To Run While It Is Happening

-- 1. Who is holding a grant, and who is queued behind them.
--    grant_time IS NULL means this query is waiting. That is your 8645 in progress.
SELECT r.session_id,
       g.requested_memory_kb / 1024  AS requested_mb,
       g.granted_memory_kb  / 1024   AS granted_mb,
       g.used_memory_kb     / 1024   AS used_mb,
       g.ideal_memory_kb    / 1024   AS ideal_mb,
       g.queue_id, g.wait_order, g.wait_time_ms,
       CASE WHEN g.grant_time IS NULL THEN 'WAITING' ELSE 'granted' END AS state,
       t.text
FROM sys.dm_exec_query_memory_grants g
LEFT JOIN sys.dm_exec_requests r ON r.session_id = g.session_id
OUTER APPLY sys.dm_exec_sql_text(g.sql_handle) t
ORDER BY g.requested_memory_kb DESC;

-- 2. The semaphore itself: how much grant memory exists, and how much is spoken for.
SELECT pool_id, resource_semaphore_id,
       target_memory_kb     / 1024 AS target_mb,
       max_target_memory_kb / 1024 AS max_target_mb,
       available_memory_kb  / 1024 AS available_mb,
       granted_memory_kb    / 1024 AS granted_mb,
       grantee_count, waiter_count
FROM sys.dm_exec_query_resource_semaphores;

The column that usually solves it is the gap between requested_mb and used_mb. A query that asked for 4 GB and used 40 MB did not need the memory, it needed a better estimate, and while it held that grant everything behind it was queuing. That single query is the cause of the 8645s, and it will not look like a problem in any duration-based report, because it ran fine. The step-by-step version of this, including an incident where the host rather than the setting was the cause, is Troubleshoot RESOURCE_SEMAPHORE Waits.

The Settings Worth Checking

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)',
               'min memory per query (KB)', 'index create memory (KB)');

SELECT total_physical_memory_kb / 1024 AS server_physical_mb,
       available_physical_memory_kb / 1024 AS server_available_mb
FROM sys.dm_os_sys_memory;

SELECT physical_memory_in_use_kb / 1024 AS sql_using_mb,
       large_page_allocations_kb / 1024 AS large_pages_mb,
       memory_utilization_percentage
FROM sys.dm_os_process_memory;

Two settings cause more of these than hardware does:

• max server memory set low and forgotten. Often set during a build, or copied from a smaller server, and never revisited. On the instance measured for this post it was 2048 MB on a machine with 7968 MB physical. Perfectly reasonable for a lab, and exactly the shape that produces grant queuing on a server people expect to be sized for the work.
• min memory per query raised. The default is 1024 KB. Every query gets at least this, so raising it multiplies across concurrency and shrinks what is left for the queries that genuinely need a grant.

Get Memory Configuration and Usage returns the two server memory settings next to the physical RAM and what SQL Server is actually holding, on one row, which is the quicker version of the first two queries above.

For 701 specifically, also look outside SQL Server. It is an allocation failure, so anything else on the box competing for memory belongs in the investigation: another instance, an antivirus scan, a backup agent, or a VM whose memory is being reclaimed by the host.

What Not To Do

Frequently Asked Questions

Related Scripts

• Performance & Troubleshooting scripts, wait stats and memory grant checks that take the delta for you
• The Wait Types Library, including RESOURCE_SEMAPHORE and what a real reading looks like
• Lock Request Time Out (Error 1222), the other timeout that gets blamed on the server when it is one statement
• SQL Server Errors: The Complete Guide, the index for the whole error series