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 (Blog | Twitter) is a Microsoft Certified Master (MCM) of SQL Server and publisher of the free SQL community ebook DBA JumpStart, an inspiring collection of advice for Data Professionals, written by 20 SQL Server experts. Awarded the Microsoft Community Contributor(MCC) award, John is a prolific blogger and can be found regularly writing about SQL Server 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.