Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sansom - SQL Server DBA in the UK

John Sansom (@SQLBrit) is a Technology Lead with the database team at Expedia, Inc. providing consulting services and support for one of the worlds largest SQL Server environments. Awarded the Microsoft Community Contributor Award (MCC) John can be found regularly blogging about Being a DBA and Professional Development over at www.johnsansom.com.

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.

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

You may also find interesting…….

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.