2016-07-11

Checking Buffer Pool Usage by Table/Index

In SQL Server, the data in table is stored in pages which has fixed size of 8 KB. Whenever there is a need of a page (for read or write) the page is first read from the disk and bought to memory location called "Buffer Pool". Below sql script can be used to check the buffer pool usage by table and index in the current database, this script calculates the usage by counting the no. of rows in sys.dm_os_buffer_descriptors. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server.
 SELECT COUNT(*) AS cached_pages_count, COUNT(*) * 8 / 1024 AS cachedUsedMB,
obj.[object_id], obj.name AS tableName, idx.index_id, idx.name AS indexName
FROM sys.dm_os_buffer_descriptors AS bd
JOIN (
        SELECT p.[object_id], OBJECT_NAME(p.[object_id]) AS name, p.index_id, au.allocation_unit_id
        FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.[type] = 1 OR au.[type] = 3)
        UNION ALL
        SELECT p.[object_id], OBJECT_NAME(p.[object_id]) AS name, p.index_id, au.allocation_unit_id
        FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.[partition_id] AND au.[type] = 2
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
JOIN sys.indexes AS idx ON idx.[object_id] = obj.[object_id] AND idx.index_id = obj.index_id
WHERE database_id = DB_ID()
GROUP BY obj.[object_id], obj.name, idx.index_id, idx.name
ORDER BY cached_pages_count DESC;


No comments:

Post a Comment