How much memory is each SQL Server database using?

As you know SQL Server stores database data and index pages in memory in an area know as the Buffer Pool.

You can use the DMV sys.dm_os_buffer_descriptors to see how much of the buffer pool memory is being used by which database. This snippet will tell you how many clean and dirty (modified since last checkpoint or read from disk) pages from each database are in the buffer pool. You can modify further. 

SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' ENDAS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) ENDAS '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

No comments:

Post a Comment