Give Memory Back to the OS? Why SQL Server Doesn’t

This is a guest post by Brian Gale. SQL Server doesn’t give memory back to the OS when the memory is idle. This post shows what it keeps in that memory and why that is by design.

Brian Gale has worked with SQL Server since 2010, on versions from SQL Server 2000 through 2017. They are primarily a database administrator who handles backups, maintenance, tuning, installation and upgrades.

Memory Pressure

If you run SQL Server, you have likely met memory pressure. It happens when the processes on a server compete for memory. The competitors can be SQL Server, the operating system, the antivirus and the backup tool.

Sometimes the pressure is expected, because the machine is small and SQL Server uses what the machine allows. Other times a setting is wrong. Several instances on one machine can over-allocate it. SQL Server can also share the machine with IIS, SSRS or SSIS, and those programs need memory too. Settings fix some of it. If they don’t, the server needs more RAM.

How SQL Server Takes and Keeps Memory

By design, SQL Server asks for memory as it needs it. Once it has the memory, it keeps it. It can shrink when Windows reports low memory or when you lower max server memory. It doesn’t hand pages back because they are idle.

The default of max server memory is 2147483647 MB, about 2 petabytes. SQL Server reads that as permission to keep asking. If Windows has memory to give, SQL Server takes it. When Windows has none left, SQL Server removes old objects from its own memory to make room. It keeps the freed memory for itself. A restart of the service returns all of it to the operating system. Then the memory grows again as queries run. Set max server memory to a realistic value for that reason.

The next query shows the memory SQL Server holds right now. TargetMB is how much it is willing to hold, and CommittedMB is how much it holds. The last column is the setting. The values are one reading from a test server with 32 GB, so yours will differ.

SELECT CONVERT(decimal(12,1), p.physical_memory_in_use_kb / 1024.0) AS SqlInUseMB,
       CONVERT(decimal(12,1), i.committed_kb / 1024.0) AS CommittedMB,
       CONVERT(decimal(12,1), i.committed_target_kb / 1024.0) AS TargetMB,
       c.value_in_use AS MaxServerMemoryMB
FROM sys.dm_os_process_memory AS p
CROSS JOIN sys.dm_os_sys_info AS i
CROSS JOIN sys.configurations AS c
WHERE c.name = N'max server memory (MB)';

The setting is still the default, 2147483647. The target is far below it, because the target follows the memory that Windows can spare.

Why SQL Server Is Greedy

SQL Server is greedy for memory because memory is fast. Reading data from memory is much faster than reading it from disk. SQL Server keeps what it reads. It pulls a page from memory when it can reuse it.

When the limit is reached, or Windows refuses more memory, SQL Server starts to purge. The older a page is and the fewer requests it gets, the sooner it goes.

What Sits in the Buffer Pool

Most of that memory is the buffer pool. It stores table data in 8 KB pages. A page needs a disk read the first time someone asks for it. It needs another only after it left the pool. Then it stays for as long as it lives in memory.

Page life expectancy measures that. It is the number of seconds a page stays in the buffer pool without a reference. A page that stays longer means more requests find their data in memory. The next query reads the counter. The value is one reading from the test server.

SELECT cntr_value AS PageLifeExpectancySeconds
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy' AND object_name LIKE N'%Buffer Manager%';

Look Inside the Buffer Pool

The view sys.dm_os_buffer_descriptors has one row for every page in the buffer pool. The query below groups those rows by page type. The variable chooses the scope. With 1 it counts only the current database. With 0 it counts every database on the instance. It needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

The demo creates a database named BufferHoldDemo. It holds a small table of blends, and some of them have a long description.

IF DB_ID(N'BufferHoldDemo') IS NULL CREATE DATABASE BufferHoldDemo;
GO
USE BufferHoldDemo;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Blends;
CREATE TABLE dbo.Blends (BlendID int NOT NULL CONSTRAINT PK_Blends PRIMARY KEY, Region varchar(30) NOT NULL, Description varchar(max) NULL);
DECLARE @batch int = 0;
WHILE @batch < 50
BEGIN
    INSERT INTO dbo.Blends (BlendID, Region, Description)
    SELECT @batch * 100 + n, CONCAT('Region ', n % 12),
           CASE WHEN n % 10 = 0 THEN REPLICATE(CONVERT(varchar(max), 'leaf and bud '), 1000) END
    FROM (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS x;
    SET @batch += 1;
END;

DECLARE @OnlyThisDatabase bit = 1;
SELECT bd.page_type AS PageType,
       COUNT(*) AS PagesInMemory,
       CONVERT(decimal(9,2), COUNT(*) / 128.0) AS MemoryMB
FROM sys.dm_os_buffer_descriptors AS bd
WHERE @OnlyThisDatabase = 0 OR bd.database_id = DB_ID()
GROUP BY bd.page_type
ORDER BY PagesInMemory DESC;

The numbers come from one run and change a little from run to run. DATA_PAGE holds row data except the large values. INDEX_PAGE holds index entries. IAM_PAGE records which extents an allocation unit owns. TEXT_MIX_PAGE and TEXT_TREE_PAGE hold the large values. The first row shows where the memory of this database goes. Set the variable to 0 to see the same split for the whole instance.

To find the tables behind those pages, read Memory Used by Each Table in the SQL Server Buffer Pool.

Set Max Server Memory

Write down the current value before you change anything. The statements below set a limit of 28 GB. That leaves 4 GB on a 32 GB machine that runs only SQL Server. The number is an example. Choose one that leaves room for Windows and every other program on your machine. The undo is the same statement with the value you wrote down. The comment in the block covers the other setting.

-- Write down the current value first:
-- SELECT value_in_use FROM sys.configurations WHERE name = N'max server memory (MB)';
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'max server memory (MB)', 28672;
RECONFIGURE;
-- Undo: run the second EXEC again with the value you wrote down.
-- The first EXEC leaves show advanced options on. Set it back to the value you wrote down, if you changed it.

Should SQL Server Give Memory Back When It Is Idle?

You could argue that SQL Server should give memory back to the OS when it isn’t using it. It would then lose the cache that makes it fast, and it would read the same pages from disk again. Memory that SQL Server holds is not lost. SQL Server gives memory back when Windows signals that it is short.

What to Remember

SQL Server takes memory as it needs it and keeps it. A server that does not give memory back to the OS is working as designed. Set max server memory to a realistic value, so that Windows and the other programs keep their share. Read the buffer pool by page type to see what the held memory contains.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'BufferHoldDemo') IS NOT NULL
BEGIN
    ALTER DATABASE BufferHoldDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BufferHoldDemo;
END;

Held memory is not wasted memory, it is a cache SQL Server already paid for.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.