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.


About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

17 Responses to A DMV a Day – Day 1

  1. Cameron says:

    Great idea. Looking forward to April.

  2. Mark says:

    You might want to change the 128 to 128.0 to avoid the integer division. On a database made up of mostly small tables [like AdventureWorks] it could be misleading otherwise.Or not. For most production databases, it makes sense to not have it.By itself, I don\’t really see how this DMV shows the effectiveness of data compression, though. This is just buffer pool info. If the buffer count for a particular table shows as 3921, it is what it is. Compression isn\’t being measured, and there isn\’t anything to compare the 3921 against.Good luck with your 30-day experiment. I\’m also looking forward to April. No fooling.

  3. Glenn says:

    Hi Mark,It indirectly shows the effectiveness of data compression. As an experiment you take an existing table with no compression and copy it to another table. Then use Page compression on the clustered index of that table. Finally, run select statements that cause clustered index scans on both tables, and run today\’s DMV query. You will see how much less space is used in the buffer pool by the Page compressed table.

  4. Stefan says:

    You can use is_modified to separate between dirty and clean pages.http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Whats-in-the-buffer-pool.aspxSample Query:select ObjectName ,ISNULL(Clean,0) as CleanPages ,ISNULL(Dirty,0) as DirtyPages ,str(ISNULL(Clean,0)/128.0,12,2) CleanPagesMB ,str(ISNULL(Dirty,0/128.0),12,2) as DirtyPagesMBfrom (select Case WHEN GROUPING(t.object_id) = 1 then \’=> Sum\’ ELSE Quotename(OBJECT_SCHEMA_NAME(t.object_id)) + \’.\’ + Quotename(OBJECT_NAME(t.object_id)) END as ObjectName ,CASE WHEN bd.is_modified = 1 THEN \’Dirty\’ ELSE \’Clean\’ END AS \’PageState\’ ,COUNT (*) AS \’PageCount\’ from sys.dm_os_buffer_descriptors bd inner join sys.allocation_units as allc on allc.allocation_unit_id = bd.allocation_unit_id inner join sys.partitions part on allc.container_id = part.partition_id inner join sys.tables t on part.object_id = t.object_id where bd.database_id = DB_ID() Group by GROUPING sets ((t.object_id,bd.is_modified),(bd.is_modified)) )pgspivot (sum(PageCount) for PageState in ([Clean],[Dirty])) as pvt

  5. Jag says:

    thx for this, but I have a question, any help is much appreciated…I ran this useful query on one of our production servers and found a clustered index (CIX) occupying 2.2GB in the buffer pool! The CIX is only on an ID column (int), which I know isn\’t 2.2GB in size. Why is this CIX occupying so much space?thxjt

  6. Shahryar says:

    Can you put all of them together into one big DMV blog? These are great.

  7. Glenn says:

    I will probably publish a blog post that links to all of the thirty days when I am done.

  8. Pingback: 31 Days of SSIS – The Introduction | Strate SQL

  9. Pingback: SQL Server Central

  10. Pingback: 31 Days of SSIS – The Introduction | SQLServerPedia

  11. Pingback: Writing A Month Long Blog Series | Strate SQL

  12. Pingback: Powershell Day 1 | Vinay Thakur – Sql Server DBA

  13. Pingback: A SQL Server Hardware Nugget A Day – Day 1 | Glenn Berry's SQL Server Performance

  14. clement says:

    I thought this was a blog about SSAS’s DMVs (Dynamic Management View) which are DMX queries to run under Analysis Services and not Database Engine like this one.
    As DMV does not accept alias for tables, the “AS a” part of the query returned an error.
    Anyway what is intend to work on a classic database won’t work on an OLAP one.
    a+, =)
    Configuration :
    SQL Server 2008 r2
    BIDS 2008

  15. Pingback: SQL Server Objects In Memory VS Disk | BarDev

  16. Pingback: A SQL Server DBA myth a day: (1/30) in-flight transactions continue after a failover | Paul S. Randal

  17. Pingback: A SQL Server Hardware Tidbit a Day – Day 1 - Glenn Berry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s