30%이상 조각난 인덱스 조회

30%이상 조각난 인덱스 조회

SELECT 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 S.AVG_FRAGMENTATION_IN_PERCENT > 30 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시 ORDER BY [Fragmentation %] DESC