How Dirty or Clean is SQL SERVER’s Memory? – Interview Question of the Week #229

Question: How much clean and dirty data is in SQL Server’s buffer pool? Group buffer descriptors by database and count the modified and unmodified pages.

This followed my post on clean and dirty pages by object. Readers with many databases asked where to begin. They wanted the same information grouped by database, including system databases and tempdb.

SELECT d.name AS DatabaseName,
       COUNT_BIG(*) AS TotalPagesInBuffer,
       CAST(COUNT_BIG(*) * 8.0 / 1024 AS decimal(18,2)) AS BufferMB,
       SUM(CONVERT(bigint, CASE WHEN bd.is_modified = 1 THEN 1 ELSE 0 END)) AS DirtyPages,
       SUM(CONVERT(bigint, CASE WHEN bd.is_modified = 0 THEN 1 ELSE 0 END)) AS CleanPages,
       CAST(SUM(CONVERT(bigint, CASE WHEN bd.is_modified = 1 THEN 1 ELSE 0 END))
            * 8.0 / 1024 AS decimal(18,2)) AS DirtyMB,
       CAST(SUM(CONVERT(bigint, CASE WHEN bd.is_modified = 0 THEN 1 ELSE 0 END))
            * 8.0 / 1024 AS decimal(18,2)) AS CleanMB
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.databases AS d ON d.database_id = bd.database_id
GROUP BY d.name
ORDER BY TotalPagesInBuffer DESC;

Each counted page is 8 KB. A clean page can be discarded and reread without first writing changed data; a dirty page has modifications that need writing to its data file. This is unrelated to dirty reads or whether a transaction has committed.

The report describes cached data pages visible in this DMV. It is not all SQL Server process memory, all query grants or all memory-optimized table storage. Databases without cached descriptors don’t appear, and pages that cannot join to sys.databases are outside this report.

I usually start with an appropriate user database and then inspect its objects and workload. The largest total isn’t automatically a problem, and a high dirty-page count isn’t a request to flush the cache. Compare samples with workload and storage behavior.

If you would like to investigate the workload together, see my Comprehensive Database Performance Health Check. For the underlying concepts, see listing dirty pages and what a clean buffer means.

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