DMV Queries - Get Top N Objects Consuming Buffer Memory

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:       10 May 2010/29 Jun 2012

Description

Retrieves details of the top N objects that consume memory from the buffer pool.  An optional database name parameter can be used to restrict the results to a single database, otherwise the results will be from all databases.
 

Code

Function:


DROP FUNCTION dbo.udf_GetTopNBufferMemoryObjects
GO

CREATE FUNCTION dbo.udf_GetTopNBufferMemoryObjects
                                      
(
                                          
@n INT,
                                          
@DatabaseName SYSNAME
                                      
)
RETURNS @TopNBufferMemoryObjects TABLE
  
(
      
DatabaseName            SYSNAME NULL,
      
ObjectName              SYSNAME NULL,
      
IndexName               SYSNAME NULL,
      
IndexType               VARCHAR(100) NULL,
      
Buffered_Page_Count     INT NULL,
      
Buffer_MB               INT NULL
   )
AS
/***************************************************************
Purpose:   To retrieve the top N greatest query plan consumers of buffer
           memory in a database.  If no database parameter is provided, then
           results are for the whole SERVER
          
           The following DMVs are used:
               sys.dm_os_buffer_descriptors    - contains one row per page in the buffer pool
               sys.allocation_units &          - to ensure only non-dropped AUs are included in the results
               sys.partitions                  - (the AU cleanup process is asynchronous)        
          
          
Author:        Unknown
History:   12 May 2010 - Initial Issue
           26 Jun 2012 - ChillyDBA - Converted to a table valued function
            with optional number or results (@N) and optional Database Name parameters

****************************************************************/

BEGIN

   INSERT
@TopNBufferMemoryObjects
      
(
          
DatabaseName,
          
ObjectName,
          
IndexName,
          
IndexType,
          
Buffered_Page_Count,
          
Buffer_MB
      
)
      
  
      
  
SELECT TOP (@n)
      
DB_NAME(database_id)    AS DatabaseName,
      
obj.[name]              AS ObjectName,
      
i.[name]                AS IndexName,
      
i.[type_desc]           AS IndexType,
      
COUNT(*)                AS Buffered_Page_Count,
      
COUNT(*) * .00781     AS Buffer_MB
  
FROM sys.dm_os_buffer_descriptors AS bd
  
INNER JOIN
      
(
          
SELECT
              
OBJECT_NAME(OBJECT_ID) AS name,
              
index_id ,
              
allocation_unit_id,
              
OBJECT_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)  -- for in-row or row-overflow data
          
UNION ALL
          
SELECT
              
OBJECT_NAME(OBJECT_ID) AS name,
              
index_id ,
              
allocation_unit_id,
              
OBJECT_ID
          
FROM sys.allocation_units AS au
          
INNER JOIN sys.partitions AS p
              
ON au.container_id = p.hobt_id
              
AND au.TYPE = 2  -- for LOB data
      
) AS obj
      
ON bd.allocation_unit_id = obj.allocation_unit_id
  
LEFT JOIN sys.indexes i
      
ON i.OBJECT_ID = obj.OBJECT_ID
      
AND i.index_id = obj.index_id
  
WHERE database_id = ISNULL(DB_ID(@DatabaseName), database_id)
  
GROUP BY
      
DB_NAME(database_id),
      
obj.name,
      
obj.index_id ,
      
i.[name],
      
i.[type_desc]
  
ORDER BY
      
Buffered_Page_Count DESC

   RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetTopNBufferMemoryObjects (25, 'AdventureworksDW')
SELECT * FROM dbo.udf_GetTopNBufferMemoryObjects (50, NULL)



ċ
udf_GetTopNBufferMemoryObjects.sql
(3k)
Andy Hughes,
Jun 29, 2012, 10:33 AM
Comments