Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server error 701 'There's insufficient system memory to run this query': find which memory is short

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Error 701 means SQL Server failed to allocate enough memory to run a query. Microsoft states that "In most cases, the transaction that failed isn't the cause of this error", so tuning that one query is not the first thing to try. The shortage comes from one of three places, and the checks below tell you which.

If the error appears once and clears, Microsoft says there might be "a short-lived memory issue that resolved itself" and you might not need to act. Act when it recurs, hits several connections and lasts seconds or longer.

What the error means

Microsoft lists event 701 under source MSSQLSERVER, symbolic name NOSYSMEM, message text "There's insufficient system memory to run this query." The Learn page groups the causes into three:

  • External or OS memory pressure. Something outside the SQL Server process is using memory. Microsoft explains that SQL Server responds to Windows low-memory signals by reducing its own memory, "Very few other applications respond", so SQL Server shrinks while the other software does not, and components then fail to get memory.
  • Internal pressure not from SQL Server components. Code that runs inside the SQL Server process but is not part of the engine: linked server providers, SQLCLR, extended procedures, OLE Automation (sp_OA*), and security or monitoring software that injects DLLs. Microsoft's example is a linked server caching 20 million rows. No memory clerk reports it, yet the process memory is high.
  • Internal pressure from SQL Server components. One of the hundreds of components tracked as memory clerks holds a large share.

Error 701 is a failed allocation. A query that is waiting for a memory grant is a different symptom, and none of the checks below is about grant queues.

Likely causes

Cause What you see How to confirm
Other software uses the host's memory Available MB low; SQL Server's working set falling Memory:Available MB and Process:Working Set for other processes
Code inside the SQL Server process Process private bytes much higher than SQL Server's total server memory Compare the two counters; list loaded modules
Large memory clerk One clerk dominates pages_kb Query sys.dm_os_memory_clerks repeatedly
Max or min server memory set badly Cap too high for the host, or too low for the load Server memory configuration options
Memory overcommitted on a virtual machine Guest sees little free memory while the host is short Hypervisor memory counters; Microsoft points to guidance on overcommit detection
Resource Governor limits Errors only for one workload group Resource pool and workload group settings
Many concurrent large queries Errors during peak concurrency Concurrent sessions and running queries

Prove the cause before changing anything

All of the following only read. Collect over time, not once, because Microsoft says to collect periodic outputs.

  1. Performance Monitor counters. Microsoft's list: Memory: Available MB; Process: Working Set; Process: Private Bytes; SQL Server: Memory Manager (all counters); SQL Server: Buffer Manager (all counters). Microsoft supplies a sample using Get-Counter with \Memory\Available MBytes, \Process(*)\Working Set and \Process(*)\Private Bytes.
  2. Memory clerks. Run Microsoft's query on a schedule while the errors occur:
SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC
  1. DBCC MEMORYSTATUS. Microsoft names it as a more detailed alternative: "you can observe the more detailed DBCC MEMORYSTATUS output and the way it changes when you see these error messages."
  2. System and Application event logs. Microsoft supplies Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*" and the same for Application.
  3. In-process modules. Microsoft gives tasklist /M /FI "IMAGENAME eq sqlservr.exe" and SELECT * FROM sys.dm_os_loaded_modules. Look for modules you did not expect.
  4. The private-bytes comparison. Without Lock Pages in Memory, most SQL Server memory shows in Process: Private Bytes for the sqlservr instance. If Private Bytes is significantly above SQL Server: Memory Manager: Total Server Memory (KB), Microsoft says the difference is likely from a DLL such as a linked server, extended procedure or SQLCLR. Microsoft states that with Lock Pages in Memory this comparison is harder and approximate.

For an automated capture, Microsoft names PSSDIAG (the Perfmon collector and the SQL Memory Error collector) and SQL LogScout (the Memory scenario).

Take each sample with a timestamp and keep the files. One sample taken after the error has passed shows a healthy server, and the comparison between samples is what shows the shortage building. Compare the clerk listing taken just before an error with one taken in a quiet period; the clerk that grows between them is the one to investigate. If no clerk is large and the private bytes figure is also close to total server memory, return to the first group and look at the rest of the host.

Read the results in plain terms. If Available MB is low and another process holds the memory, the pressure is external. If private bytes exceed total server memory by a large margin, it is in-process. If one clerk dominates, it is internal to the engine.

Fix

External pressure. Microsoft's guidance: fix code or configuration of less critical applications, stop or reschedule them, or move them to another server.

Disruptive step: stopping or moving another application interrupts its users. Confirm with its owner. To back out, restart it.

In-process code. For a linked server that may be consuming memory, Microsoft says you can run it out of process by disabling the Allow inprocess option, noting that not all OLE DB providers run out of process. For OLE Automation objects, setting context to 4 (Local .exe OLE server only) runs the object outside SQL Server.

Disruptive step: changing a linked server provider or OLE Automation context changes how queries behave and may fail if the provider cannot run out of process. Test first. To back out for a linked server, re-enable the Allow inprocess option on its provider. To back out for OLE Automation, set context back to the value your code used before (the value you changed from), and note that value before you change it.

Memory clerk examples from Microsoft.

  • MEMORYCLERK_SQLQERESERVATIONS: find queries with huge memory grants and optimise them with indexes, rewrites such as removing ORDER BY, or query hints.
  • CACHESTORE_SQLCP (ad hoc plans): parameterise queries by stored procedures, sp_executesql or forced parameterisation.
  • CACHESTORE_OBJCP: identify which procedures, functions or triggers use the memory; this can mean redesign.
  • OBJECTSTORE_LOCK_MANAGER: find queries that take many locks, shorten transactions, or check whether lock escalation is disabled.

Disruptive step: FORCED parameterization is a database-level setting, so it changes how every query in that database is handled. Microsoft's 701 page names it without describing its effects, so test it on a copy first. Note the current setting before you change it, and to back out set it back to that value. Rewriting queries, adding indexes or adding hints also changes behaviour: test each change and keep the previous query text or index definition to restore.

Quick relief. Microsoft lists: check max server memory and min server memory and "consider increasing max server memory if possible"; if max server memory has not been set, especially with Lock Pages in Memory, set it to leave memory for the operating system; check the workload; and on a virtual machine make sure memory is not overcommitted. It also names these caches to clear: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE and DBCC FREEPROCCACHE.

Disruptive step: the three DBCC FREE commands discard cached data. Microsoft's DBCC FREEPROCCACHE page says that clearing the plan cache evicts all plans and incoming queries compile new ones, which "can cause a sudden, temporary decrease in query performance". There is no way back except letting the caches refill. Use them as a short bridge, not as a fix.

Disruptive step: a max server memory value that is too high can bring back the host-level pressure. Record the old value to put back, and put it back if Available MB falls again.

Resource Governor. If used, Microsoft recommends checking pools and workload groups so they do not limit memory too drastically.

Disruptive step: changing a Resource Governor pool or workload group changes the memory available to that workload for every session in it. Record the old limits first. To back out, restore those values.

Confirm the fix

  • Keep the same counters and the clerk query running for a full busy period and check that error 701 no longer appears in the error log.
  • Compare pages_kb for the offending clerk before and after.
  • Check that Available MB stays above its earlier low point.

Example (illustrative): sql01 has 128 GB of RAM. During the nightly load, Process: Private Bytes for sqlservr reads 92 GB while SQL Server: Memory Manager: Total Server Memory reads 64 GB. The difference is 92 - 64 = 28 GB, which is 30.4% of the private bytes held outside the engine's accounting. sys.dm_os_loaded_modules shows a third-party provider DLL. The linked server using it is set to run out of process, and the next night's difference falls. Memory clerks showed nothing large, which matches Microsoft's description of an in-process module.

What to watch afterwards

  • Keep the counter capture for at least one full cycle of the heaviest job.
  • Re-run the clerk query after schema or workload changes.
  • Re-check max server memory after changes to the VM's size.
  • Treat a rise in a single clerk across days as a trend worth a ticket.

Risks and limits

These commands have not been run or lab-tested. Microsoft's page is general; it gives no fixed numbers for how large a difference is a problem, so the 28 GB figure above is illustrative only. Tasklist, Get-Counter and Get-EventLog are Windows commands. On SQL Server on Linux, use the DMVs and Linux tools instead.

Do not restart SQL Server as the first response; it clears the memory state that shows which memory was short. Do not raise max server memory on a host that is already short, and do not clear caches repeatedly to keep errors away.

Sources