[MSSQL][DMV] 테이블,Index의 Object가 사용하는 buffer 정보 분석

[MSSQL][DMV] 테이블,Index의 Object가 사용하는 buffer 정보 분석

 

 

1. 버퍼 캐쉬 메모리를 사용하는 테이블,Index 사용량 알아내기

SELECT OBJECT_NAME(p.object_id) AS [ObjectName], p.object_id, p.index_id,i.name AS [Index Name], COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id LEFT JOIN sys.indexes AS i WITH (NOLOCK) ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE b.database_id = db_id() AND p.object_id > 100 GROUP BY p.object_id, p.index_id,i.name ORDER BY buffer_count DESC;