查询表或索引占 缓存池的情况

 SELECT count(*)*8 AS cached_pages_kb ---1个页通常为8KB
    ,obj.name ,obj.index_id,b.type_desc,b.name
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.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
        LEFT JOIN sys.indexes b on b.object_id = obj.object_id AND b.index_id = obj.index_id
 
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id ,b.name,b.type_desc
ORDER BY cached_pages_kb DESC;