Index Memory Usage

USE master

SELECT tables.name,
       indexes.name,
       COUNT(*) AS cached_pages_count,
       COUNT(*) / 128 AS cached_mb
  FROM sys.dm_os_buffer_descriptors
 INNER JOIN (
       SELECT partitions.object_id,
              partitions.index_id,
              allocation_units.allocation_unit_id
         FROM sys.allocation_units
        INNER JOIN sys.partitions ON allocation_units.container_id = partitions.hobt_id
          AND (allocation_units.type = 1
           OR allocation_units.type = 3)
        UNION ALL
       SELECT partitions.object_id,
              partitions.index_id,
              allocation_units.allocation_unit_id
         FROM sys.allocation_units
        INNER JOIN sys.partitions ON allocation_units.container_id = partitions.partition_id
          AND allocation_units.type = 2
       ) AS obj ON dm_os_buffer_descriptors.allocation_unit_id = obj.allocation_unit_id
 INNER JOIN sys.indexes ON obj.object_id = indexes.object_id
   AND obj.index_id = indexes.index_id
 INNER JOIN sys.tables ON indexes.object_id = tables.object_id
WHERE dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY tables.name,
      indexes.name
ORDER BY cached_pages_count DESC
OPTION (RECOMPILE)