Dirty Pages – How to List Dirty Pages From Memory in SQL Server? – Interview Question of the Week #223

Question: How do you list dirty pages in SQL Server memory? Read sys.dm_os_buffer_descriptors and filter is_modified = 1.

An attendee asked this during my SQL Server Performance Tuning Practical Workshop. I had already written about viewing dirty pages in memory, so we returned to that example.

A dirty page has changed in memory and needs its changed contents written to the data file. This is different from a dirty read. It also doesn’t mean the transaction is uncommitted: transaction commitment and flushing a data page are separate events.

Match the pages to objects in the current database

SELECT DB_NAME() AS database_name,
       s.name AS schema_name, o.name AS object_name,
       p.object_id, p.index_id,
       bd.file_id, bd.page_id, bd.page_type, bd.page_level
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.objects AS o ON o.object_id = p.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE bd.database_id = DB_ID()
  AND bd.is_modified = 1
ORDER BY s.name, o.name, p.index_id, bd.file_id, bd.page_id;

Run this in the database you are investigating. Buffer descriptors cover the instance, but allocation units and partitions belong to the current database. Filtering the database before interpreting those joins avoids assigning another database’s pages to the wrong object.

IN_ROW_DATA and ROW_OVERFLOW_DATA allocation units use the partition’s hobt_id. LOB_DATA uses partition_id. This report lists dirty pages that can be matched to catalog objects; it is not a complete inventory of every internal buffer-pool page.

Checkpoint writes dirty pages as part of recovery management. It does not commit an open user transaction. Nor should we expect an active database’s report to stay empty after a checkpoint: other sessions can continue changing pages.

This read-only inspection doesn’t need a trace flag or a manual checkpoint. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE; earlier versions require VIEW SERVER STATE.

For a different memory question, see queries waiting for memory allocation. Memory grants for query execution are a separate topic from modified data pages in the buffer pool.

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