[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;