Get Top N Objects Consuming Buffer Memory

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