Cached Pages and Size by Database

A query that reports the number of cached pages and buffer pool usage in GB for each database.

Published Jan 17, 2005

Which databases are actually living in the buffer pool? This query counts the cached 8KB pages per database from dm_os_buffer_descriptors and converts the total to gigabytes - a quick view of how your memory is divided between databases.

SELECT
	db_name(database_id) as [Database],
	COUNT(0) AS CachedPages,
	Convert(Decimal(16, 2), ((COUNT(0) * 8192.0) / 1024.0 / 1024.0 / 1024.0)) AS [BufferPoolUsage (GB)]
FROM
	sys.dm_os_buffer_descriptors
WHERE
	db_name(database_id) IS NOT NULL
GROUP BY
	database_id
ORDER BY
	CachedPages DESC