Whilst perusing the forums over at SQL Server Central today I stumbled across an interesting question regarding how to identify how much memory is being used by SQL Server on a per database level.
As you know SQL Server stores database data and index pages in memory in an area know as the Buffer Pool. Using my trusty friend Google, I researched an answer to this question on ServerFault provided by Paul Randal. The solution made use of the SQL Server Dynamic Management View (DMV) sys.dm_os_buffer_descriptors
As you know I’m a big fan of using SQL Server DMV’s and so I wanted to take note of this handy SQL code snippet here in order to share it with you.
SELECT (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State', (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name', COUNT (*) AS 'Page Count' FROM sys.dm_os_buffer_descriptors GROUP BY [database_id], [is_modified] ORDER BY [database_id], [is_modified]; GO
