A DMV a Day – Day 1

Just as an experiment, I am going to post a different DMV query every day for the next 30 days.  They will hopefully be interesting and useful, but I cannot guarantee that I won’t hit the same DMV more than once during the thirty day period.

The first DMV 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

This query tells you which objects are using the most memory in your buffer pool. It is filtered by the current database. This query works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    -- Breaks down buffers by object (table, index) in the buffer pool
    SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id], 
    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()
    GROUP BY p.[object_id], p.index_id
    ORDER BY buffer_count DESC;

It shows the table or indexed view name, the index ID (which will be zero for a heap table), and the amount of memory used in the buffer pool for that object. It is a good way to see the effectiveness of data compression in SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Enterprise Edition.