Jeremy Carter

The dmv sys.dm_os_buffer_descriptors gives a detailed look into how the buffer pages in SQL server are being used. Aside from just satisfying a morbid curiosity into where the memory is going, the details can be a helpful troubleshooting tool. When I first look at a server I need some way of deciding where I should concentrate my tuning efforts and get the biggest bang for the buck. One way I decide where to start is by looking at the memory usage of each db. The breakdown can also be very handy providing information to use in determining sql server consolidations projects or helping decide when it is time to break that db out to its own server.

At a high level we can tell the sql process is using x amount of ram and the following script lets you start to break that down to individual databases. This script takes sys.dm_os_buffer_descriptors and formats the output so that each database is listed with the size of the clean, dirty, and total buffers.

SELECT
[DatabaseName],
	ISNULL([Dirty],'0') AS [Dirty],
	ISNULL([Clean],'0') AS [Clean],
	ISNULL([Total],'0') AS [Total]
FROM(
SELECT
  (CASE WHEN ([database_id] = 32767) THEN 'Resource Database'
		ELSE ISNULL(DB_NAME (database_id),'Total')
	END) AS 'DatabaseName',
  (CASE WHEN ([is_modified] = 1) THEN 'Dirty'
		WHEN ([is_modified] = 0) THEN 'Clean'
		ELSE 'Total'
	END) AS 'State',
  COUNT (*)/128 AS 'SizeInMB'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified] WITH CUBE
) AS SourceTable
PIVOT(SUM([SizeInMB]) FOR [State] IN (Clean, Dirty, Total)) AS PivotTable
ORDER BY [DatabaseName]

The results will look something like this: