Printed 2017/08/17 11:27PM

How much memory is each SQL Server database using?


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.

   (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];

You may also find interesting…….

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.