Consommation du buffer par les tables ou index

Cette requête vous donne la consommation du buffer cache par table ou index dans la base courante

/*------------------------------------------------------------------- 
[SCRIPT] Buffer Consumption
[DATABASE] current 
[DESCRIPTION] Consommation du buffer par objet (table/index) pour la base courante 
[MAJ PAR] DATAFLY - Arian Papillon 
[DATE] 20180905 
-------------------------------------------------------------------*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 25 
obj.[name],
i.[name],
i.[type_desc],
count_big(*)AS Buffered_Page_Count ,
count_big(*) * 8192 / (1024 * 1024) as Buffer_MB
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.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id() -- base courante
AND OBJECTPROPERTYEX(obj.object_id,'IsMSShipped')=0
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

/*-------------------------------------------------------------------

[SCRIPT] Buffer Consumption

[DATABASE] current

[DESCRIPTION] Consommation du buffer par objet (table/index) pour la base courante

[MAJ PAR] DATAFLY - Arian Papillon

[DATE] 20180905

-------------------------------------------------------------------*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 25

obj.[name],

i.[name],

i.[type_desc],

count_big(*)AS Buffered_Page_Count ,

count_big(*) * 8192 / (1024 * 1024) as Buffer_MB

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.hobt_id

AND au.type = 2

) AS obj

ON bd.allocation_unit_id = obj.allocation_unit_id

LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id

WHERE database_id = db_id() -- base courante

AND OBJECTPROPERTYEX(obj.object_id,'IsMSShipped')=0

GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]

ORDER BY Buffered_Page_Count DESC