[MSSQL] 인덱스 조각화(Fragmentation) 조회

MSSQL에서 인덱스 조각화(Fragmentation)를 조회 하고, 30보다 클 경우 재작성(Rebuild), 그 이하일 경우 재구성(Reorganize)하는 스크립트까지 생성하는 스크립트 입니다.

단순히, Percentage를 보고, 재구성, 재작성하는 스크립트를 만들어주는 쿼리는 한번의 SELECT로 가능하지만, 이게 어떤 인덱스 이길래 이렇지? 궁금한 경우가 있어서, 임시테이블에 집어 넣고, 해당 인덱스를 구성하는 컬럼명도 나열하도록 작성해 봤습니다.

프로시저 등으로 만들어 놓고, 데이터베이스에 유휴 시간이 일정하다면 스케줄러와 Dynamic Query를 활용하여 인덱스 확인 및 재구성(재작성) 자동화를 구현할 수도 있겠죠.

데이터베이스 유휴 시간이 거의 없는 건 함정.

(MSSQL 엔터프라즈 버전에서는 online indexing 이 가능하긴 합니다.)

DECLARE @fragmentation_per NUMERIC(10,2);

SET @fragmentation_per = 5; /* 조각율을 지정 */

SELECT [데이터베이스] = DB_NAME(ps.database_id),
       [테이블ID]     = ps.object_id,
       [테이블명]     = OBJECT_NAME(ps.object_id),
       [인덱스ID]     = ps.index_id,
       [인덱스명]     = b.name,
       [인덱스컬럼]   = CONVERT(VARCHAR(4000), NULL),
       [조각율]       = ps.avg_fragmentation_in_percent,
       [스크립트]     = 'ALTER INDEX ' + b.name + ' ON ' + OBJECT_NAME(ps.object_id) + 
                        CASE WHEN ps.avg_fragmentation_in_percent > 30
                             THEN ' REBUILD WITH (FILLFACTOR = 70)' ELSE ' REORGANIZE' END +
                        '; /*' +
                        CONVERT(VARCHAR, CONVERT(NUMERIC(10,2), ps.avg_fragmentation_in_percent)) +
                        ' FRAG PERCENT */'
       INTO #TEMP
  FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS ps
       INNER JOIN SYS.INDEXES AS b ON ps.object_id = b.object_id
                                  AND ps.index_id = b.index_id
 WHERE ps.database_id = DB_ID()
   AND ps.avg_fragmentation_in_percent >= 5
   AND b.name IS NOT NULL;

DECLARE @object_id       INT,
        @index_id        INT,
        @index_column_id INT,
        @index_columns   VARCHAR(4000);

WHILE EXISTS ( SELECT * FROM #TEMP WHERE [인덱스컬럼] IS NULL )
BEGIN

    SELECT @object_id = [테이블ID],
           @index_id = [인덱스ID],
           @index_column_id = 0,
           @index_columns = ''
      FROM #TEMP
     WHERE [인덱스컬럼] IS NULL;

    WHILE EXISTS ( SELECT 1
                     FROM SYS.INDEX_COLUMNS ic
                    WHERE ic.object_id = @object_id
                      AND ic.index_id = @index_id
                      AND ic.index_column_id > @index_column_id )
    BEGIN
        SELECT TOP 1
               @index_column_id = ic.index_column_id,
               @index_columns = @index_columns +
                                CASE WHEN LEN(@index_columns) > 0
                                     THEN ', ' ELSE '' END + c.name +
                                CASE WHEN ic.is_descending_key = 1
                                     THEN '(-)' ELSE '' END
          FROM SYS.INDEX_COLUMNS ic
               INNER JOIN SYS.COLUMNS c
                       ON c.object_id = ic.object_id
                      And c.column_id = ic.column_id
         WHERE ic.object_id = @object_id
           AND ic.index_id = @index_id
           AND ic.index_column_id > @index_column_id
         ORDER BY index_column_id ASC;

         PRINT @index_column_id;
    END;

    UPDATE #TEMP
       SET [인덱스컬럼] = @index_columns
     WHERE [테이블ID] = @object_id
       AND [인덱스ID] = @index_id;
    
END;

SELECT *
  FROM #TEMP
 ORDER BY 7 DESC;

DROP TABLE #TEMP;

블로그에 올리기 위해 라인 당 글자수를 조절해 보려 했는데 그건 참 쉽지 않습니다. :)