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.