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)