MS-SQL 인덱스 상태 보기

인덱스 상태 보기

SELECT TOP 10 

            DB_NAME() AS 'DatabaseName'

          , OBJECT_NAME(S.OBJECT_ID) AS 'TableName'

          , I.NAME AS 'IndexName'

          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S

 INNER JOIN SYS.INDEXES AS I

 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID

 WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스

 AND I.NAME IS NOT NULL -- HEAP은 무시

 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시

 ORDER BY [Fragmentation %] DESC