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

A DMV A Day – Day 30

The DMV for Day 30 is sys.dm_os_buffer_descriptors, which is described by BOL as:

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

This DMV is used to determine how your buffer pool memory is being used, i.e., which databases and which indexes are using the most memory in the buffer pool.  This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    -- Get total buffer usage by database
    SELECT DB_NAME(database_id) AS [Database Name],
    COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id > 4 –- exclude system databases
    AND database_id <> 32767 –- exclude ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC;
    
    
    -- Breaks down buffers used by current database by 
    -- object (table, index) in the buffer cache
    SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],  
    p.index_id, COUNT(*)/128 AS [buffer size(MB)],  
    COUNT(*) AS [buffer_count] 
    FROM sys.allocation_units AS a
    INNER JOIN sys.dm_os_buffer_descriptors AS b
    ON a.allocation_unit_id = b.allocation_unit_id
    INNER JOIN sys.partitions AS p
    ON a.container_id = p.hobt_id
    WHERE b.database_id = DB_ID()
    AND p.[object_id] > 100
    GROUP BY p.[object_id], p.index_id
    ORDER BY buffer_count DESC;

The first query rolls up buffer pool usage by database. It allows you to determine how much memory each database is using in the buffer pool. It could help you to decide how to deploy databases in a consolidation or scale-out effort. The second query shows you how much memory is being used by individual indexes in the current database. It could be used to help you decide which indexes might be candidates for data compression (with SQL Server 2008 and 2008 R2 Enterprise Edition).

Comments

Posted by Dukagjin Maloku on 30 April 2010

Is this the last DMV or it will continue more, however the last one is very useful and thank you for sharing (30 DMVs) till today!

Posted by Anonymous on 30 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, A DMV A Day ??? Day 30 - Glenn Berry's SQL Server Performance         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 30 April 2010

Pingback from  Dew Drop &#8211; April 30, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Jason Brimhall on 30 April 2010

Most excellent query.  I like this one a lot.

Posted by Gethyn Ellis on 30 April 2010

Glenn I have really enjoyed that last 30 days of DMV based blog posts I have learned a lot...thanks

Posted by ms4446 on 30 April 2010

Great series of blog posts.. I really enjoyed them. :-)

Posted by Seth Phelabaum on 1 May 2010

I really enjoyed this series, but I suppose like all good things it has to come to an end.  

Posted by Glenn Berry on 1 May 2010

Thanks for all of the kind words everyone. I still need to write the recap for the series, but that should go up by Monday.

Posted by Megistal on 3 May 2010

Oh great queries! Didn't had those in my t-sql administration collection! It will be very helpful!

Leave a Comment

Please register or log in to leave a comment.