[SQL Server] Afficher l'espace mémoire utilisée par chaque objet de la base

 

-- =============================================

-- Author:      Zouhaier KHARROUBI

-- Create date: 20/04/2020

-- Description: Afficher l'espace mémoire utilisée par chaque objet de la base

-- =============================================

USE master;

GO

 

;WITH R1 AS

(

SELECT

[Object] = T2.name,

[Type] = T2.type_desc,

[Index] = COALESCE(T3.name, ''),

[Index_Type] = T3.type_desc,

T1.[object_id],

T1.index_id,

au.allocation_unit_id

FROM

sys.partitions AS T1

INNER JOIN

sys.allocation_units AS au

ON T1.hobt_id = au.container_id

INNER JOIN

sys.objects AS T2

ON T1.[object_id] = T2.[object_id]

INNER JOIN

sys.indexes AS T3

ON T2.[object_id] = T3.[object_id]

AND T1.index_id = T3.index_id

WHERE

au.[type] IN (1,2,3)

AND T2.is_ms_shipped = 0

)

SELECT

R1.[Object],

R1.[Type],

R1.[Index],

R1.Index_Type,

buffer_pages = COUNT_BIG(b.page_id),

buffer_mb = COUNT_BIG(b.page_id) / 128

FROM

R1

INNER JOIN

sys.dm_os_buffer_descriptors AS b

ON R1.allocation_unit_id = b.allocation_unit_id

WHERE

b.database_id = DB_ID()

GROUP BY

R1.[Object],

R1.[Type],

R1.[Index],

R1.Index_Type

ORDER BY

buffer_pages DESC;