SQL: Buffer Cache Contents
Author: jason
Date: 2014-04-08 12:38:44
Category: Technical

Here is a script that will show what Indexes are currently cached in RAM in the Buffer Cache. This script would need to be run per each Database to get the full view of everything in cache.

*note i need to update this with sys.indexes so it really shows the index name, right now its just the table name*





Show the cache for 1 database
Code

SELECT
DB_NAME(DB_ID()) DBName
, name IndexName
, count(1)/128 [Cached (MB)]
, index_id IndexID
FROM sys.dm_os_buffer_descriptors AS bd WITH (NOLOCK)
INNER JOIN
(
SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + object_name(object_id)
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + object_name(object_id)
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id() --AND DB_NAME(DB_ID()) NOT IN ('master', 'tempdb', 'msdb', 'model')
GROUP BY name, index_id
HAVING Count(*) > 128
ORDER BY 3 DESC;



Use sp_MSforeachdb to loop through and show the Cache for all databases




Code

CREATE TABLE #CacheInfo (
DBName VARCHAR(255)
, IndexName VARCHAR(255)
, CacheMB INT
, IndexID INT)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #CacheInfo
SELECT
DB_NAME(DB_ID()) DBName
, name IndexName
, count(1)/128 [Cached (MB)]
, index_id IndexID
FROM sys.dm_os_buffer_descriptors AS bd WITH (NOLOCK)
INNER JOIN
(
SELECT name = OBJECT_SCHEMA_NAME(object_id) + ''.'' + object_name(object_id)
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT name = OBJECT_SCHEMA_NAME(object_id) + ''.'' + object_name(object_id)
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id() --AND DB_NAME(DB_ID()) NOT IN (''master'', ''tempdb'', ''msdb'', ''model'')
GROUP BY name, index_id
HAVING Count(*) > 128
ORDER BY 3 DESC';

SELECT * FROM #CacheInfo
ORDER BY CacheMB DESC;
DROP TABLE #CacheInfo;


http://sqlmonitormetrics.red-gate.com/top-buffer-cache-object/




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009