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;