깡민쓰~IT Story

-- 가장 조각이 많이 난 인덱스 TOP 10
 
 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