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

The severities carry the same message. 701 is severity 19, which is a resource error the server logs as serious. 8645 is severity 17, an insufficient-resources condition that resolves on its own. The wording of 8645 says as much: “Rerun the query.” SQL Server is telling you the condition was temporary. 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 tells you to look at RESOURCE_SEMAPHORE in sys.dm_os_wait_stats. That is the right wait type and the wrong source, and it is worth showing why with a real measurement rather than asserting it.

Here is what that DMV said on an instance that had been up for one day. The last column is the one that matters: cumulative wait time as a percentage of how long the instance had been running.

Read that RESOURCE_SEMAPHORE row cold and it is alarming: 216 queries, one of them waiting eight hours for a memory grant. It is also meaningless. The instance had been up 24 hours. A single wait cannot last eight hours inside a window where nothing waited eight hours, and a cumulative total cannot be 164% of the time available to accumulate it.

Two things produce that, and both are ordinary:

• Waits accumulate per task, in parallel. Many things wait at once, so totals routinely exceed wall-clock time. SOS_WORK_DISPATCHER at 2424% is the clearest tell: that is background workers idling, and it is present on every healthy instance.
• The wait clock does not stop when the machine does. Anything that suspends the host, a VM pause, a laptop sleeping, a snapshot, leaves a wait running across the gap and records a single enormous value. That eight-hour maximum is a machine that was asleep, not a query that was patient.

So the sanity check comes first, before any wait-stats conclusion:

-- Any wait whose total exceeds uptime is telling you about accumulation or a
-- suspended host, not about pressure. Check this BEFORE reading the numbers.
DECLARE @up_ms bigint = (SELECT DATEDIFF_BIG(ms, sqlserver_start_time, GETDATE())
                         FROM sys.dm_os_sys_info);

SELECT TOP 10 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 waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

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 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.

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

Common 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