Fix SQL Server 'insufficient system memory in resource pool internal'

The 30-Second Fix: Check Your Max Server Memory Setting

What's actually happening here is that SQL Server's Resource Governor, which manages memory for internal operations, can't get the memory it needs. The most common cause is that you've set max server memory too high, leaving no headroom for the OS or other processes.

1. Open SSMS and connect to the instance.
2. Right-click the server in Object Explorer, choose Properties.
3. Go to the Memory page.
4. Look at Maximum server memory (in MB). If it's set to the default (2147483647), that's your problem — SQL will eat all available RAM and starve the OS.
5. Set it to a value that leaves at least 2-4 GB for the OS. For a server with 32 GB RAM, set it to 28672 (28 GB). For 64 GB, set it to 61440 (60 GB).
6. Click OK and restart the SQL Server service.

The reason this works: Resource Governor's 'internal' pool is granted a portion of the total memory. If the OS is fighting for memory, the internal pool can't get its share. Capping max server memory gives the OS breathing room.

This fix works in about 80% of cases where the error appears during normal operation, especially on servers that have been running for weeks without a restart.

The 5-Minute Fix: Disable Resource Governor (If You Don't Use It)

If the simple fix didn't do it, or if you've never set up Resource Governor manually, it might be running with default settings that are too restrictive. Here's how to check and disable it.

-- Check if Resource Governor is enabled
SELECT * FROM sys.dm_resource_governor_configuration;

-- If it's enabled and you're not using it, disable it
ALTER RESOURCE GOVERNOR DISABLE;

Run the first query. If the is_enabled column returns 1, you have Resource Governor active. Unless you've deliberately configured workload groups and classifier functions, it's just sitting there causing trouble.

Disabling it removes the resource pool constraints entirely. SQL Server will use its default memory management, which is generally fine for most workloads.

A real-world scenario: I've seen this error pop up on a SQL 2019 instance after a Windows update changed the memory footprint of other services. Disabling Resource Governor resolved it instantly.

The 15+ Minute Fix: Rebuild Indexes and Clear the Plan Cache

If you're still stuck, the internal pool might be fragmented or holding onto stale query plans that consume memory. This is more common on busy OLTP systems where plans churn constantly.

Step 1: Check Memory Pressure

SELECT * FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

Look at the top memory clerks. If OBJECTSTORE_LPFCACHE or CACHESTORE_SQLCP is huge, your plan cache is bloated.

Step 2: Clear the Plan Cache (Careful)

DBCC FREEPROCCACHE;

This wipes all cached query plans. It's a blunt instrument — all queries will recompile, which can cause a temporary CPU spike. Do this during a low-traffic window.

The reason step 2 works: The plan cache lives in the internal pool. If it's full of plans for queries you ran once weeks ago, it's squeezing out the memory needed for new compilations.

Step 3: Rebuild Heavily Fragmented Indexes

Fragmented indexes cause queries to read more pages, which generates more memory pressure. Find and rebuild the worst offenders.

SELECT OBJECT_NAME(ps.object_id) AS table_name,
       i.name AS index_name,
       ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30;

For each index with >30% fragmentation, run:

ALTER INDEX index_name ON schema.table_name REBUILD;

After rebuilding, re-run the memory clerk query. You'll see the size of the plan cache drop because the optimizer creates more efficient plans.

Step 4: Update Statistics

Outdated statistics can cause the optimizer to underestimate or overestimate row counts, leading to excessive memory grants. Update all statistics:

EXEC sp_updatestats;

This ensures the optimizer makes sane decisions, reducing the chance of a single query requesting a massive memory grant that empties the internal pool.

When to Call It Quits and Restart

Sometimes the fastest fix is just restarting the SQL Server service. This resets the internal pool and clears all memory clerks. It's not elegant, but if you're in a production emergency and you've got a maintenance window, it buys you time to investigate properly.

The real long-term fix is monitoring memory pressure before it becomes an error. Set up alerts for Page life expectancy dropping below 300 seconds, and watch sys.dm_os_ring_buffers for memory pressure notifications.

If you're still seeing the error after all this, you're likely looking at a hardware or OS-level memory leak from another process. Check the Windows Event Log for memory-related warnings, and consider adding RAM if the server is genuinely undersized for the workload.