Dirty Pages

Clean pages can be fl ushed from cache using dbcc dropcleanbuffers, which can be handy when you’re troubleshooting development and test environments because it forces subsequent reads to be fulfi lled from disk, rather than cache, but doesn’t touch any dirty pages.

You can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:

SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC

Buffer Pool

The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV.

For example, the following query will return the amount of data cache usage in MB per database:

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC



Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure


MSSQL$:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool.
MSSQL$:Buffer Manager\Page Life Expectancy: This is the amount of time, in
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.