Index 리빌드 및 통계 업데이트

-- 인덱스 조각화 조회

SELECT

            distinct 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 -- 시스템 개체 무시

 and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10

 

-- 조각화 테이블에 대한 인덱스 리빌드 및 통계 업데이트.

set quoted_identifier  OFF

DECLARE @tablename varchar(255)

DECLARE @tablename_header varchar(255)

declare @sql varchar(1000)

DECLARE tnames_cursor CURSOR FOR SELECT

            distinct 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 -- 시스템 개체 무시

 and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10

OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @tablename

WHILE (@@fetch_status <> -1)



    BEGIN

     IF (@@fetch_status <> -2)





         BEGIN

        SELECT @tablename_header = "Updating Indexes FOR table " + RTRIM(UPPER(@tablename))

        PRINT @tablename_header

  SET @sql = 'ALTER INDEX ALL ON ' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '

    EXEC (@sql)

  PRINT (@sql)

     END

     FETCH NEXT FROM tnames_cursor INTO @tablename

END

PRINT " "

PRINT " "

SELECT @tablename_header = "************* NO MORE TABLES" +

 " *************"

PRINT @tablename_header

PRINT " "

PRINT "Indexes have been updated FOR ALL tables."

DEALLOCATE tnames_cursor

go

 -- 통계 업데이트

 EXEC sp_updatestats @resample='resample';

GO

set quoted_identifier  ON

 

결과 로그

각 테이블 인덱스 별 업데이트 여부

 

리빌드 후 인덱스 조각화 조회

 

조각화 90이상인 테이블들이 많았으나 리빌드 후 50이하로 떨어졌다.