CHECKPOINT Impact on Memory: Watch Dirty Pages Go to Zero

CHECKPOINT impact on memory is easy to state. SQL Server writes every dirty page to disk, and the pages stay in memory as clean pages. Counting the pages before and after makes it visible.

Dirty Pages and Clean Pages

SQL Server reads and changes data pages in memory, in an area called the buffer pool. A page that has been changed since it was last written to disk is dirty. A page that matches the disk is clean. A dirty page is safe, because the transaction log already holds the change. It is only not yet in the data file.

The log is the reason SQL Server can wait. Writing every change to the data file at once would be slow. So it batches the writes. The batch is the checkpoint.

What CHECKPOINT Does

A checkpoint writes the dirty pages of one database to its data files. The CHECKPOINT impact on memory is small. SQL Server runs checkpoints on its own. A new database on SQL Server 2016 and later uses indirect checkpoints, with a target of 60 seconds. You can also run CHECKPOINT by hand. It works on the current database only.

Two things follow. A recent checkpoint means less work after a crash, because less log needs to be redone. And a checkpoint does not throw anything out of memory. The pages stay. They only change from dirty to clean, which is what you see below.

Count the Pages

The demo creates a database named CheckpointMemoryDemo and one table. The line with TARGET_RECOVERY_TIME = 0 SECONDS turns off the indirect checkpoint for this database only. Without it, SQL Server flushes most pages in the background during the load. Too few are left to count.

IF DB_ID(N'CheckpointMemoryDemo') IS NULL CREATE DATABASE CheckpointMemoryDemo;
GO
ALTER DATABASE CheckpointMemoryDemo SET TARGET_RECOVERY_TIME = 0 SECONDS;
GO
USE CheckpointMemoryDemo;
GO
DROP TABLE IF EXISTS dbo.Readings;
CREATE TABLE dbo.Readings (ReadingID int IDENTITY(1,1) PRIMARY KEY, Station char(8) NOT NULL, Reading int NOT NULL, Note char(100) NOT NULL);
CHECKPOINT;

Now load 12,000 rows and count the pages of this database by state. The view sys.dm_os_buffer_descriptors lists every page in the buffer pool. Reading it needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. The filter on the database ID keeps the count to the demo.

INSERT INTO dbo.Readings (Station, Reading, Note)
SELECT 'ST' + RIGHT('000000' + CONVERT(varchar(6), n % 50), 6), n, 'x'
FROM (SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;

SELECT CASE is_modified WHEN 1 THEN 'Dirty' ELSE 'Clean' END AS PageState, COUNT(*) AS Pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY is_modified
ORDER BY is_modified DESC;

The load left 269 dirty pages in this run. The 265 clean pages are system pages and table pages that were already written. Your numbers will differ a little. Now run the checkpoint and count again.

CHECKPOINT;

SELECT CASE is_modified WHEN 1 THEN 'Dirty' ELSE 'Clean' END AS PageState, COUNT(*) AS Pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY is_modified
ORDER BY is_modified DESC;

The dirty row is gone. All pages are clean. The clean count rose from 265 to 536. That is a gain of 271, about the 269 pages that were dirty. The checkpoint wrote those pages to disk and removed none from memory. That is the whole impact of CHECKPOINT on memory.

Why a Clean Count Can Still Drop

A clean count can fall after a checkpoint, and the checkpoint is not the cause. Clean pages leave memory for other reasons. SQL Server needs room for another database, or, under memory pressure, the lazy writer frees the least used pages. A count without a database filter also mixes in the pages of every other database. Those change all the time.

To empty the clean pages on purpose, you need a different command. The post DBCC DROPCLEANBUFFERS Impact on Memory: See a Cold Cache in Action shows what it does. It also explains why you run CHECKPOINT first.

Checkpoints and Failover

A checkpoint shortens crash recovery. After an unplanned stop, SQL Server redoes the log written since the last checkpoint. A failover cluster instance runs that recovery on the new node, so frequent checkpoints keep the redo short. SQL Server already bounds that work with automatic checkpoints, and the indirect checkpoint adds a target.

An availability group works differently. The secondary replays the log all the time, so the redo queue matters more than checkpoints on the primary. A manual CHECKPOINT before a planned failover is not a tuning step.

You could argue that nobody needs to run CHECKPOINT by hand, because SQL Server does it. That is right in production. In a test it gives you a known state. Run it before you measure memory, before a backup test or before you empty the cache.

What to Remember

The CHECKPOINT impact on memory is a change of state, not a loss. Dirty pages become clean pages. Count them with sys.dm_os_buffer_descriptors and filter by database. The view is slow on a big server, so keep it for a test server or a quiet hour.

When you finish with the demo, run the cleanup script.

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

A checkpoint is not a memory cleaner, it is a save button.

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