SQL Server Buffer Pool Memory used by each Object and each Database

The sys.dm_os_buffer_descriptors DMV returns information about all the data pages cached in the SQL Server buffer pool.

To see the number of pages cached in SQL Server buffer pool and the amount of RAM used in MB:

select count(*) AS Page_Count
,count(*) * 8 / 1024 as Cached_Size_MB
from sys.dm_os_buffer_descriptors

To see the number of pages cached in SQL Server buffer pool and the amount of RAM used in MB for each database:

SELECT count(*)AS Page_Count
,count(*) * 8 / 1024 as Cached_Size_MB
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Page_Count DESC;

To see the count of pages cached in SQL Server buffer pool for each object in the current database:

SELECT count(*) AS Page_Count
,object_name ,index_id
FROM sys.dm_os_buffer_descriptors AS bfd
INNER JOIN
(
SELECT object_name(object_id) AS object_name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS alu
INNER JOIN sys.partitions AS pt
ON alu.container_id = pt.hobt_id
AND (alu.type = 1 OR alu.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bfd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY object_name, index_id
ORDER BY Page_Count DESC;

Related post:
Remove all clean buffers from the buffer pool using DBCC DROPCLEANBUFFERS

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">