Dirty Pages and Clean Pages in the SQL Server Buffer Pool

Dirty pages are pages in memory that SQL Server has changed but has not yet written to the data file. Clean pages match the file. Counting both per database and per table shows where the buffer pool holds changes. One command brings the dirty count to zero.

What a Dirty Page Is

SQL Server reads a page into memory, the buffer pool, and works on it there. An UPDATE changes the page in memory and writes a log record. The page is now dirty, because it differs from the copy in the data file. The log record protects the change, so SQL Server does not need to write the page at once.

A checkpoint, or the lazy writer, writes them to the file later. After the write, the page is clean again and stays in memory for the next read. Dirty pages are normal on a busy server. They are work in progress, not damage.

Count Dirty Pages Per Database

The view sys.dm_os_buffer_descriptors has one row for every page in the buffer pool. Reading it needs the VIEW SERVER STATE permission. The column is_modified is 1 for a dirty page. This query groups the rows by database. Database number 32767 is the hidden Resource database, so the query gives it a name.

SELECT CASE database_id WHEN 32767 THEN N'Resource database' ELSE DB_NAME(database_id) END AS DatabaseName,
       COUNT(*) AS TotalPages,
       SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) AS DirtyPages,
       SUM(CASE WHEN is_modified = 0 THEN 1 ELSE 0 END) AS CleanPages,
       CONVERT(decimal(9,2), COUNT(*) * 8 / 1024.0) AS BufferMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPages DESC;

Run this query with care on a large server. The view has one row per buffered page. A server with hundreds of gigabytes of memory holds millions of them. Run it once, outside the busiest hour.

Count Dirty Pages Per Table

To see which table holds them, join the buffer rows to the allocation units, the partitions and the indexes. The demo below builds a small database and a view with that join. A new database uses indirect checkpoints with a 60 second target, which write changed pages in the background. The demo sets the target to 0. The changes then stay visible longer.

USE master;
GO
IF DB_ID(N'BufferPageDemo') IS NULL CREATE DATABASE BufferPageDemo;
GO
ALTER DATABASE BufferPageDemo SET RECOVERY SIMPLE;
ALTER DATABASE BufferPageDemo SET TARGET_RECOVERY_TIME = 0 SECONDS;
GO
USE BufferPageDemo;
GO
DROP VIEW IF EXISTS dbo.BufferByObject;
DROP TABLE IF EXISTS dbo.Seeds;
CREATE TABLE dbo.Seeds (SeedID int IDENTITY(1,1) CONSTRAINT PK_Seeds PRIMARY KEY, Variety char(200) NOT NULL, Packets int NOT NULL);
INSERT INTO dbo.Seeds (Variety, Packets)
SELECT TOP (20000) 'Basil', 1 FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE VIEW dbo.BufferByObject
AS
SELECT OBJECT_NAME(p.object_id) AS ObjectName,
       i.name AS IndexName,
       COUNT(*) AS TotalPages,
       SUM(CASE WHEN bd.is_modified = 1 THEN 1 ELSE 0 END) AS DirtyPages,
       SUM(CASE WHEN bd.is_modified = 0 THEN 1 ELSE 0 END) AS CleanPages,
       CONVERT(decimal(9,2), SUM(CASE WHEN bd.is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024.0) AS DirtyMB
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au ON au.allocation_unit_id = bd.allocation_unit_id
JOIN sys.partitions AS p ON (au.type IN (1, 3) AND au.container_id = p.hobt_id)
                         OR (au.type = 2 AND au.container_id = p.partition_id)
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE bd.database_id = DB_ID() AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
GROUP BY p.object_id, i.name;

The join has two arms because the allocation unit types store their container differently. Row data and overflow data join on the hobt id. Large object data joins on the partition id. Read the view right after the insert.

SELECT * FROM dbo.BufferByObject;

This is one run, and your dirty count will differ. The checkpoint that SQL Server runs by itself has already cleaned part of the table. Now change every row, and read the view again.

UPDATE dbo.Seeds SET Packets = 2;
SELECT * FROM dbo.BufferByObject;

Now 607 of the 635 pages are dirty, about 4.74 MB. Every row sat on a page, and the UPDATE touched every page. The pages are changed in memory and not yet in the file.

Watch CHECKPOINT Clean the Pages

A manual CHECKPOINT writes every dirty page of the current database to the file. It does not remove the pages from memory. They stay in the buffer pool as clean pages.

CHECKPOINT;
SELECT * FROM dbo.BufferByObject;

The table still has 635 pages in memory, and none of them is dirty. To clean dirty pages in general, run CHECKPOINT in the database that holds them. It needs db_owner, db_backupoperator or sysadmin rights, and it writes the pages to disk. Expect a burst of writes on a busy database.

What Not to Do on a Production Server

Do not follow a checkpoint with DBCC DROPCLEANBUFFERS to empty the buffer pool. The command drops the clean pages of the whole instance, so every query starts again from disk. It leaves dirty pages alone, which is why a test runs CHECKPOINT first. Use it on a test server when you need a cold cache, and never on a server that people use.

You could argue that per-table counts are noise, because the buffer pool changes every second. That is fair for one reading. A few readings, taken a minute apart, show which tables stay in memory. They also show which tables keep a high dirty count. A count that never falls between checkpoints is a reason to check the write speed of the disk.

What to Remember

Dirty pages are changes that have not reached the file. Count them per database with sys.dm_os_buffer_descriptors, and per table with the join to the allocation units. Treat the numbers as a snapshot, and read them more than once.

Use CHECKPOINT to turn them into clean pages, and leave DBCC DROPCLEANBUFFERS to test servers. When you finish the demo, run the cleanup script.

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

A dirty page is not a problem, it is work that SQL Server has not written down yet.

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