[MSSQL] 모든 인덱스 사이즈를 조회하는 방법

SQL Server 인덱스 조회에 필요한 뷰

---

sys.indexes

인덱스와 힙 개체를 포함합니다.

이 때 힙은 클러스터 인덱스가 없는 테이블을 의미합니다.

sys.partitions

파티션ID에 속한 개체 내부의 인덱스ID를 포함합니다.

sys.allocation_units

데이터베이스 할당된 유닛을 포함합니다.

하나의 유닛에 몇 페이지(KB)가 할당되었는지를 알 수 있습니다.

유닛의 used_pages 가 1 이라면 8KB 가 사용되었다는 의미로 해석할 수 있습니다.

SQL Server 모든 인덱스 크기를 조회하는 쿼리

---

위 3개의 뷰를 조인하여 SQL Server 전체 인덱스의 크기를 조회하는 쿼리입니다.

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    CASE i.index_id WHEN 1 THEN 'Clustered Index' ELSE 'NonClustered Index' END AS IndexType,
    SUM(au.used_pages) * 8 AS 'Indexsize(KB)'
FROM
    sys.indexes AS i
    INNER JOIN sys.partitions AS p
       ON p.OBJECT_ID = i.OBJECT_ID
      AND p.index_id = i.index_id
    INNER JOIN sys.allocation_units AS au
      ON au.container_id = p.partition_id
WHERE
    i.index_id != 0                               -- not heap
    and OBJECT_SCHEMA_NAME(i.object_id) != 'sys'  -- not system table
GROUP BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_SCHEMA_NAME(i.object_id),
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id

AdventureWorks 데이터베이스를 조회했을 때 아래와 같은 결과를 얻을 수 있습니다.

SQL Server 모든 인덱스 용량 조회하는 쿼리

SQL Server 모든 인덱스 크기 조회하는 쿼리

SQL Server 모든 인덱스 용량 조회하는 방법

SQL Server 모든 인덱스 크기 조회하는 방법

SQL Server 전체 인덱스 용량 조회하는 쿼리

SQL Server 전체 인덱스 크기 조회하는 쿼리

SQL Server 전체 인덱스 용량 조회하는 방법

SQL Server 전체 인덱스 크기 조회하는 방법