[MS-SQL] 인덱스 리빌드(Rebuild), 리오그나이즈(Reorganize) 스케쥴러

안녕하세요. 이번포스팅에서는 인덱스 리빌드와 리오그나이즈에 대해서 설명하고 적용하는 방법을 공유하려고 합니다.

솔루션을 도입, 개발하였을때 사용자가 속도가 체감적으로 느려졌다라고 느끼실겁니다. 보통 사용하다보면 데이터의 양이 많아져 속도가 느려질수 밖이 없습니다. 그러나 인덱스를 사용하여 검색속도를 증가시켜 속도를 향상 시킬수 있습니다. 그러나 데이터가 변경되고 사용하다보면 조각화가 발생됩니다. 이러한 조각화를 해결 하기 위해 리빌드와 리오그나이즈를 사용합니다. 

1. 인덱스 리빌드(Rebuild)

• 인덱스를 완전히 새롭게 다시 생성합니다. 즉, 기존 인덱스를 삭제하고 새로운 인덱스를 만들어 데이터를 정렬합니다. 
• 리빌드 과정은 리소스를 많이 사용하며, 데이터베이스의 성능에 영향을 줄 수 있습니다.
• 리빌드는 인덱스 조각화가 심한경우 사용하는것이 좋습니다.

2. 인덱스 리오그나이즈(Reorganize)

• 인덱스의 논리적 조각화를 줄이기 위해 인덱스 페이지를 재구성합니다. 기존 인덱스를 유지하면서 페이지를 정렬하고 공간을 재활용합니다.
• 리오그나이즈 과정은 리소스 사용이 상대적으로 적으며, 데이터베이스 성능에 큰 영향을 주지 않습니다.
• 인덱스 조각화가 비교적 적은 경우에 사용 하는것이 좋습니다.

3. 인덱스를 조회하여 주기적으로 리빌드 또는 리오그나이즈를 실행

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[INDEX_REBUILD]

AS
DECLARE
    @VAL_SQL            NVARCHAR(MAX)
   ,@VAL_TABLE_NM       VARCHAR(MAX)
   ,@VAL_SCHEMA_NM      VARCHAR(MAX)
   ,@VAL_INDEX_NM       VARCHAR(MAX)
   ,@VAL_INDEX_PERCENT  DECIMAL(5,2)
BEGIN 

    DECLARE CUR CURSOR FOR
    SELECT T04.name AS SCHEMA_NAME
          ,T03.name AS TABLE_NAME
          ,T02.name AS INDEX_NAME
          ,T01.avg_fragmentation_in_percent AS INDEX_FRAG_PERCENT
      FROM SYS.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) T01
           INNER JOIN  SYS.indexes T02 ON T01.object_id = T02.object_id AND T01.index_id = T02.index_id
           INNER JOIN SYS.tables T03 ON T03.object_id = T02.object_id
           INNER JOIN SYS.schemas T04 ON T04.schema_id = T03.schema_id
     WHERE 1=1
       AND T02.index_id > 0 AND T02.is_disabled = 0 AND T02.is_hypothetical = 0
       AND T02.type_desc <> 'XML';
    OPEN CUR
    FETCH NEXT FROM CUR INTO @VAL_SCHEMA_NM , @VAL_TABLE_NM, @VAL_INDEX_NM, @VAL_INDEX_PERCENT
    WHILE @@FETCH_STATUS = 0 BEGIN
        -- 조각화 수치가 30이상이면 리빌드 아니면 리오그나이즈 실행
        IF @VAL_INDEX_PERCENT >= 30
            BEGIN
                SET @VAL_SQL = 'ALTER INDEX [' + @VAL_INDEX_NM + '] ON [' + @VAL_SCHEMA_NM + '].[' + @VAL_TABLE_NM + '] REBUILD;';
            END
        ELSE
        BEGIN
            SET @VAL_SQL = 'ALTER INDEX [' + @VAL_INDEX_NM + '] ON [' + @VAL_SCHEMA_NM + '].[' + @VAL_TABLE_NM + '] REORGANIZE;';
        END

        
        PRINT '실행 명령: ' + @VAL_SQL;
        EXEC sp_executesql @VAL_SQL;
        FETCH NEXT FROM CUR
        INTO  @VAL_SCHEMA_NM , @VAL_TABLE_NM, @VAL_INDEX_NM, @VAL_INDEX_PERCENT
    END;
    CLOSE CUR
    DEALLOCATE CUR
END

위의 프로시저를 생성합니다. 

해당 인덱스를 조회하여 30이상이면 리빌드, 아니면 리오그나이즈를 실행하게 됩니다.

그리고 SQL Server 에이전트를 통하여 스케쥴러를 생성합니다. 

SQL Server 에이전트 - 작업 선택 후 우클릭 하여 새작업을 만듭니다.

일반 - 새작업

그리고 단계로 넘어가 실행 할 내용을 생성합니다. 새로만들기를 클릭 후 생성한 인덱스 리빌드 프로시저를 호출합니다.

단계 설정

그리고 일정을 등록하여 언제마다 반복 작업 할 것인지 설정합니다.

일정 설정

그리고 일정 설정을 완료하여 해당 프로시저가 작업되는지 확인 합니다.

이번 포스팅은 MSSQL 인덱스 리빌드, 리오그나이즈 스케쥴러 셋팅이였습니다. 궁금하신 내용이나 잘못된 내용은 댓글이나 메일로 부탁드리겠습니다. 감사합니다.