How to fix Error 701 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 701 – Insufficient System Memory in Resource Pool

Why This Error Happens

Error 701 occurs when SQL Server’s Resource Governor has restricted a memory-intensive query to a resource pool that lacks sufficient memory to execute it, often because the pool’s MAX_MEMORY_PERCENT is set too low relative to the query’s actual demands. This can also happen when the overall SQL Server instance is under memory pressure due to competing workloads, an undersized max server memory configuration, or Windows Server 2025/Windows 11 reclaiming memory for other system processes. Additionally, plan cache bloat, excessive parallelism (high MAXDOP), or a poorly optimized query requesting a large memory grant can trigger this even when total system memory appears adequate.

Step-by-Step Resolution Guide

Step 1: Verify Current Resource Governor Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to check pool settings:
  SELECT * FROM sys.dm_resource_governor_resource_pools;
  • Identify the specific pool named in the error message and note its max_memory_percent and min_memory_percent values.

Step 2: Increase Memory Allocation for the Affected Resource Pool

  • Use ALTER RESOURCE POOL to raise the memory ceiling:
  ALTER RESOURCE POOL [YourPoolName]
  WITH (MAX_MEMORY_PERCENT = 50);
  ALTER RESOURCE GOVERNOR RECONFIGURE;
  • If the pool is not essential, consider disabling Resource Governor entirely for testing:
  ALTER RESOURCE GOVERNOR DISABLE;

Step 3: Adjust SQL Server’s Max Server Memory Setting

  • Navigate to SSMS > Right-click Server > Properties > Memory tab.
  • Ensure max server memory (MB) is not set too conservatively — leave at least 4-6 GB free for the OS on Windows Server 2025/Windows 11.
  • Alternatively, run:
  EXEC sp_configure 'max server memory (MB)', 16384;
  RECONFIGURE;

Step 4: Optimize the Offending Query’s Memory Grant

  • Use Query Store or sys.dm_exec_query_memory_grants to identify queries requesting excessive memory.
  • Add or update statistics on large tables involved in the query:
  UPDATE STATISTICS dbo.YourTable WITH FULLSCAN;
  • Consider adding a query hint to cap grant size if the issue is isolated to one query:
  OPTION (MIN_GRANT_PERCENT = 0, MAX_GRANT_PERCENT = 25)

Step 5: Check Windows Server Memory Pressure

  • Open Task Manager or Performance Monitor (perfmon.exe) and check Available MBytes under Memory counters.
  • Confirm no other applications (IIS, containers, or antivirus scanning) are consuming excessive RAM on the host.
  • Restart the SQL Server service if configuration changes require a full reload: net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

  • Re-run the original failing query and confirm Error 701 no longer appears; monitor sys.dm_resource_governor_resource_pools to verify memory usage stays within the new threshold.
  • If the error persists, consider moving the workload to the default resource pool temporarily or increasing physical RAM/VM memory allocation on the Windows Server 2025 host.