Printed 2016/02/10 07:33PM

A DMV A Day – Day 30

By GlennBerry, 2010/04/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).

Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.