Eric M Russell (10/26/2016)

---

SQL Server's buffer cache holds pages in their original compressed format.

You can query the space allocated in the page buffer cache for individual objects using the following script.

SELECT TOP 10

db_name(database_id) Database_Name,

obj.[name] Object_Name,

i.[name] Index_Name,

i.[type_desc] Index_Type,

count_big(*) * 8192 / (1024 * 1024) as Object_BufferPool_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)

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

) 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 = db_id()

GROUP BY database_id, obj.name, obj.index_id , i.[name],i.[type_desc]

ORDER BY count_big(*) DESC;