[MSSQL] 인덱스 조각화 정보 조회

 

 특정 DB 내 모든 인덱스 조각화 정보 조회
: 조회하려는 DB에서 실행

USE [DB명]
GO

SELECT db_name(db_id())                            AS DBName
               , schema_name(tmp2.schema_id)  AS SchemaName
               , tmp2.name                                    AS TableName
               , tmp3.name                                    AS IdxName
               , ROUND(tmp1.AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %' 
FROM (SELECT * 
             FROM sys.dm_db_index_physical_stats (DB_ID(db_name()), NULL, NULL, NULL, NULL)
) AS tmp1
LEFT OUTER JOIN sys.tables AS tmp2 
ON tmp1.object_id = tmp2.object_id
LEFT OUTER JOIN sys.indexes AS tmp3 
ON tmp1.object_id=tmp3.object_id AND tmp1.index_id=tmp3.index_id
WHERE 1=1
-- AND tmp1.avg_fragmentation_in_percent > 0 
AND tmp3.name IS NOT NULL -- HEAP은 무시
AND OBJECTPROPERTY(tmp1.object_id, 'IsMsShipped') = 0 -- 시스템 개체 무시
ORDER BY [Fragmentation %] DESC
GO