Large Memory Servers: How SQL Server Scans the Buffer Pool

SQL Server scans the buffer pool for several commands, and on large memory servers that scan used to hurt. SQL Server 2022 eased it by sharing the scan across several tasks.

The change is quiet. Nothing in your queries moves. A few maintenance commands that used to stall on a big machine now finish sooner.

What a Buffer Pool Scan Is

The buffer pool is the memory where SQL Server keeps the data pages it reads. Each page sits in a buffer of 8 KB. When SQL Server scans the buffer pool, it visits every buffer to find the pages of one database. Taking a database offline starts a scan. So do a backup, a restore and the DBCC commands.

The time of a scan grows with the number of buffers. On a small server nobody notices. On large memory servers, with around a terabyte, a serial scan can slow the command that started it.

See Which Commands Scan

You can watch how SQL Server scans the buffer pool on any server. It has extended events for the scan. The script creates a demo database and starts an event session that records every scan start. The next script runs four commands against the demo database. It takes the database offline and online, backs it up to NUL, which keeps nothing, and checks it with DBCC.

IF DB_ID(N'LargeMemoryScanDemo') IS NULL CREATE DATABASE LargeMemoryScanDemo;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'LargeMemoryScanWatch') DROP EVENT SESSION LargeMemoryScanWatch ON SERVER;
CREATE EVENT SESSION LargeMemoryScanWatch ON SERVER
ADD EVENT sqlserver.buffer_pool_scan_start,
ADD EVENT sqlserver.buffer_pool_scan_complete
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION LargeMemoryScanWatch ON SERVER STATE = START;

ALTER DATABASE LargeMemoryScanDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE LargeMemoryScanDemo SET ONLINE;
BACKUP DATABASE LargeMemoryScanDemo TO DISK = N'NUL' WITH COPY_ONLY;
DBCC CHECKDB (N'LargeMemoryScanDemo') WITH NO_INFOMSGS;

The event session is server wide, so the query keeps only the rows of the demo database. Creating the session needs the permission ALTER ANY EVENT SESSION, and reading it needs VIEW SERVER STATE. The ring buffer holds the events in memory.

SELECT ev.value('(@name)[1]', 'nvarchar(60)') AS EventName,
       ev.value('(data[@name="command"]/value)[1]', 'nvarchar(60)') AS Command,
       ev.value('(data[@name="operation"]/value)[1]', 'nvarchar(60)') AS Operation,
       ev.value('(data[@name="parallel_tasks"]/value)[1]', 'int') AS ParallelTasks
FROM (SELECT CAST(t.target_data AS xml) AS x FROM sys.dm_xe_session_targets AS t JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address WHERE s.name = N'LargeMemoryScanWatch') AS q
CROSS APPLY x.nodes('/RingBufferTarget/event') AS n(ev)
WHERE ev.value('(data[@name="database_id"]/value)[1]', 'int') = DB_ID(N'LargeMemoryScanDemo')
ORDER BY ev.value('(@timestamp)[1]', 'datetime2');

The offline step, the backup and the DBCC check started scans. DBCC CHECKDB started two. The online step started none. Each scan used one task, as the ParallelTasks column shows. The operation column names what the scan does: RemoveBuffersFromDB, FlushCache and MarkBuffersCopyOnWrite. The completion event did not fire. By its definition it fires only for a scan that takes longer than one second, and these scans were short.

Why Only Large Memory Servers Care

The scans above ran over a pool of a few gigabytes, so they were serial and quick. A server with a terabyte of memory changes the picture. A serial scan over more than 100 million buffers is slow. In earlier versions the error log noted a scan that took more than 10 seconds. The completion event reports the time, the command and the buffer counts. It arrived in SQL Server 2016 SP3, 2017 CU23 and 2019 CU9. Both are documented, and neither fired on the small test server.

Before SQL Server 2022, some administrators tried DBCC DROPCLEANBUFFERS. It empties the pool, so every query then reads from disk. DBCC DROPCLEANBUFFERS Impact on Memory: See a Cold Cache in Action shows that cost. The cure cost more than the problem.

What SQL Server 2022 Changed

From SQL Server 2022 the scan runs in parallel. SQL Server uses one task for each 8 million buffers, which is 64 GB of memory. Below 8 million buffers the scan stays serial, as the ParallelTasks column above shows. The test server shows where it stands.

SELECT CAST(committed_target_kb / 1048576.0 AS decimal(9,1)) AS TargetGB, committed_target_kb / 8 AS TargetBuffers FROM sys.dm_os_sys_info;

One reading on the test server showed about 7.5 GB, or about one million buffers. Your numbers differ. That reading is far below 8 million, so every scan above used one task. The table shows the same rule on larger machines, counted from the buffers per task.

The scan also helps the other commands. Restore, backup and startup run the same kind of scan. They finish sooner on the big machines. Then see what fills the pool in Buffer Pool Pages for One Table in SQL Server.

Do Not Tune for This

You could argue that this change matters to almost nobody, because few servers hold a terabyte. That is true. On a server with 64 GB or less, nothing changed, and you do not need to act. The demo shows the scans and their task count. It does not show the delay itself, because a delay needs a pool of the size in the table.

What to Remember

SQL Server scans the buffer pool for startup, backup, restore and DBCC. On large memory servers the scan can slow each of them. SQL Server 2022 splits the scan into one task per 8 million buffers. Read the scan events if a maintenance command is slow on a large machine, and leave DBCC DROPCLEANBUFFERS alone.

When you finish, run the cleanup script. It stops the event session and drops the demo database.

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'LargeMemoryScanWatch')
BEGIN
    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = N'LargeMemoryScanWatch') ALTER EVENT SESSION LargeMemoryScanWatch ON SERVER STATE = STOP;
    DROP EVENT SESSION LargeMemoryScanWatch ON SERVER;
END;
IF DB_ID(N'LargeMemoryScanDemo') IS NOT NULL
BEGIN
    ALTER DATABASE LargeMemoryScanDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE LargeMemoryScanDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LargeMemoryScanDemo';

A buffer pool scan is not a query problem, it is a cost that grows with the size of memory.

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