SQL SERVER – Dirty Pages Clean Pages and the Correct Cache Commands

Clean Pages have no outstanding data-file write, while dirty pages contain unwritten changes. A customer asked which state was better.

SELECT DB_NAME(database_id) AS DatabaseName,is_modified,COUNT_BIG(*) AS CachedPages
FROM sys.dm_os_buffer_descriptors
WHERE database_id=DB_ID() GROUP BY database_id,is_modified;

A clean page can be read without modification. It can also become clean after changed contents are written. My earlier definition required modification incorrectly. Both states are normal buffer-pool behavior.

Checkpoints write eligible dirty pages under write-ahead logging. DROPCLEANBUFFERS removes clean buffers for specialized tests. FREEPROCCACHE clears execution plans. It does not turn dirty data pages clean.

The inventory counts database buffer descriptors by modification flag. It does not describe every memory allocation or total process memory. Compare defined intervals and workloads for pressure or latency investigations.

Don’t clear shared caches or force checkpoints to improve the appearance of a count. Investigate the actual workload requirement. Keep plan-cache and data-cache scopes distinct.

Related reading

• Comprehensive Database Performance Health Check
• CHECKPOINT
• DBCC FREEPROCCACHE
• Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory
• SQL SERVER – Increasing Speed of CHECKPOINT and Best Practices
• Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
• Twitter

A clean page is not necessarily a previously modified page, it is a page without an outstanding modification to write.

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