DBCC MEMUSAGE in 2005/8 ?

I used to like using undocumented command DBCC MEMUSAGE in SQL 2000 to see which tables were using space in SQL data cache.

In SQL 2005, this command is not longer present. Instead a DMV – sys.dm_os_buffer_descriptors – can be used to display data cache contents,  but this doesn’t quite give you the same output as DBCC MEMUSAGE.

I’m also aware that you can use Quest’s spotlight tool to view a summary of data cache contents.

Using  this post by Umachandar Jayachandran  of Microsoft, I was able to create the following equivalent for SQL 2005/8. I’ve wrapped Umachandar’s original query in a CTE to produce summary information :-

;WITH memusage_CTE AS (SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type
, COALESCE(p1.object_id, p2.object_id) AS object_id
, COALESCE(p1.index_id, p2.index_id) AS index_id
, bd.row_count, bd.free_space_in_bytes, CONVERT(TINYINT,bd.is_modified) AS 'DirtyPage'
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au
ON au.allocation_unit_id = bd.allocation_unit_id
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.hobt_id = au.container_id AND au.type IN (1, 3)
) AS p1
OUTER APPLY (
SELECT TOP(1) p.object_id, p.index_id
FROM sys.partitions AS p
WHERE p.partition_id = au.container_id AND au.type = 2
) AS p2
WHERE  bd.database_id = DB_ID() AND
bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') )
SELECT TOP 20 DB_NAME(database_id) AS 'Database',OBJECT_NAME(object_id,database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages'
FROM memusage_CTE
GROUP BY database_id, object_id, index_id
ORDER BY COUNT(*) DESC

I’m not 100% happy with the results of the above query however… I’ve noticed that on a busy BizTalk messageBox database  it will return information on pages that contain GHOST rows – . ie where data has already been deleted but has yet to be cleaned-up by a background process – I’m need to investigate further why cache on this server apparently contains so much GHOST data…

For more information on the background ghost cleanup process, see this article by Paul Randall.

However, I think the results of this query should still be of interest to a DBA. I have another post to come shortly regarding an example I encountered where this information proved useful to me…

I notice in SQL 2008, sys.dm_os_buffer_descriptors gained an extra column – numa_mode – I’m interested to see how this is populated and how useful this column can be on a NUMA-enabled system. I’m assuming in theory you could use this column to help analyse how your tables are spread across Numa-enabled data-cache ?

Published 02 April 2010 12:31 by steveh99999
Filed under: ,

Comments

# Twitter Trackbacks for DBCC MEMUSAGE in 2005/8 ? - Steve Hindmarsh's SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 DBCC MEMUSAGE in 2005/8 ? - Steve Hindmarsh's SQL Blog         [sqlblogcasts.com]        on Topsy.com

# re: DBCC MEMUSAGE in 2005/8 ?

I use this query ( database specific )

SELECT top 20 obj.[name]as "Table Name" ,obj.index_id ,si.name,convert(numeric(10,2),(count(*)*8)/1024.0) AS "cached size (mb)"

FROM sys.dm_os_buffer_descriptors AS bd

   INNER JOIN

   (

       SELECT object_name(object_id) AS name

           ,index_id ,allocation_unit_id

       FROM sys.allocation_units AS au

           INNER JOIN sys.partitions AS p

               ON au.container_id = p.hobt_id

                   AND (au.type = 1 OR au.type = 3)

       UNION ALL

       SELECT object_name(object_id) AS name  

           ,index_id, allocation_unit_id

       FROM sys.allocation_units AS au

           INNER JOIN sys.partitions AS p

               ON au.container_id = p.hobt_id

                   AND au.type = 2

   ) AS obj

       ON bd.allocation_unit_id = obj.allocation_unit_id

join sys.indexes si on si.index_id = obj.index_id    and si.[object_id] = object_id(obj.name)

WHERE bd.database_id = db_id()

GROUP BY obj.name, obj.index_id,si.name

ORDER BY "cached size (mb)" DESC

09 April 2010 12:50 by GrumpyOldDBA

# re: DBCC MEMUSAGE in 2005/8 ?

GrumpyOldDBA - thanks for the comment - I compared the results of both our queries and consequently I've amended mine to include page type of TEXT_MIX_PAGE.

15 April 2010 09:16 by steveh99999

# First release of my own personal T-SQL Code Library on github

Like many (most???) T-SQL developers I keep a stash of useful code that I’ve garnered down the years

12 January 2014 22:40 by Jamie Thomson