인덱스 조각화 발생 원인

SQL Server에서 데이터베이스를 운영하다 보면 초기와 달리 성능이 점진적으로 저하되는 현상을 겪게 됩니다. 이러한 성능 저하의 주요 원인 중 하나는 인덱스 조각화(Index Fragmentation)입니다. 데이터의 빈번한 INSERT, UPDATE 작업은 데이터 페이지의 연속성을 깨뜨리고 '페이지 분할(Page Split)'을 유발하며, 이는 결국 불필요한 I/O 발생으로 이어져 시스템을 느리게 만듭니다.

인덱스 조각화 해결을 위한 주요 전략

조각화를 해결하는 방법은 크게 네 가지로 분류할 수 있습니다.

1. 기존 인덱스 삭제 후 신규 생성
2. DROP_EXISTING 옵션을 사용한 재구성
3. ALTER INDEX REBUILD 명령을 통한 인덱스 재구축
4. ALTER INDEX REORGANIZE 명령을 통한 인덱스 재정리

실무에서는 시스템 부하를 고려하여 조각화 정도에 따라 REBUILD(재구축)와 REORGANIZE(재정리)를 선택적으로 적용하는 방식을 주로 사용합니다.

자동 인덱스 최적화 저장 프로시저

아래는 데이터베이스 내의 모든 인덱스를 순회하며 조각화 비율에 따라 적절한 조치를 취하는 자동화 스크립트입니다. 조각화가 30% 이상일 경우 전체 재구축을, 10%~30% 사이일 경우 페이지 재정리를 수행합니다.

CREATE PROCEDURE [dbo].[usp_OptimizeDatabaseIndexes]
    @ResultStatus INT OUTPUT
AS
SET NOCOUNT ON
BEGIN
    DECLARE @MinPageLimit INT = 1000;      -- 일정 규모 이상의 인덱스만 대상
    DECLARE @ReorgThreshold INT = 10;      -- 재정리 기준 (%)
    DECLARE @RebuildThreshold INT = 30;    -- 재구축 기준 (%)
    
    DECLARE @TableName NVARCHAR(256);
    DECLARE @IndexName NVARCHAR(256);
    DECLARE @FragPercent FLOAT;
    DECLARE @ExecutionSql NVARCHAR(MAX);
    DECLARE @CurrentDbId INT = DB_ID();

    BEGIN TRY
        SET @ResultStatus = -1;

        -- 인덱스 물리적 상태 정보 조회 (커서 사용)
        DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT 
                obj.name AS TableName,
                idx.name AS IndexName,
                stat.avg_fragmentation_in_percent
            FROM sys.dm_db_index_physical_stats(@CurrentDbId, NULL, NULL, NULL, 'LIMITED') stat
            JOIN sys.indexes idx ON stat.object_id = idx.object_id AND stat.index_id = idx.index_id
            JOIN sys.tables obj ON stat.object_id = obj.object_id
            JOIN sys.dm_db_partition_stats part ON stat.object_id = part.object_id AND stat.index_id = part.index_id
            WHERE stat.index_id > 0 -- 힙(Heap) 제외
              AND stat.avg_fragmentation_in_percent >= @ReorgThreshold
              AND part.reserved_page_count >= @MinPageLimit;

        OPEN IndexCursor;
        FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @FragPercent >= @RebuildThreshold
            BEGIN
                -- 조각화가 심할 경우 인덱스 전체 재생성
                SET @ExecutionSql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD';
            END
            ELSE
            BEGIN
                -- 조각화가 낮을 경우 인덱스 페이지 재정리
                SET @ExecutionSql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE';
            END

            EXEC sp_executesql @ExecutionSql;

            FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent;
        END

        CLOSE IndexCursor;
        DEALLOCATE IndexCursor;
        
        SET @ResultStatus = 0;
    END TRY
    BEGIN CATCH
        SET @ResultStatus = -1;
        THROW;
    END CATCH;
END

조각화 발생 및 성능 개선 시뮬레이션

실제로 데이터를 조작하며 조각화가 어떻게 발생하는지, 그리고 관리 후 성능이 어떻게 개선되는지 확인하는 과정입니다.

-- 1. 테스트용 테이블 및 인덱스 생성
IF OBJECT_ID('FragTestTable') IS NOT NULL DROP TABLE FragTestTable;

CREATE TABLE FragTestTable (
    ID INT, 
    Padding CHAR(1000), 
    Note VARCHAR(20)
);

CREATE CLUSTERED INDEX CL_IDX_ID ON FragTestTable(ID);

-- 2. 초기 데이터 대량 삽입
DECLARE @i INT = 1;
WHILE (@i <= 1000)
BEGIN
    INSERT INTO FragTestTable (ID, Padding, Note) VALUES (@i * 10, 'Data', '');
    SET @i = @i + 1;
END

-- 3. 페이지 분할 유발을 위한 업데이트 및 중간값 삽입
UPDATE FragTestTable SET Note = 'Update Task' WHERE ID % 100 = 0;

INSERT INTO FragTestTable (ID, Padding, Note)
SELECT ID + 5, 'NewData', 'Fragment' FROM FragTestTable WHERE ID < 500;

-- 4. 인덱스 조각화 상태 확인
SELECT 
    avg_fragmentation_in_percent, 
    page_count, 
    record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('FragTestTable'), NULL, NULL, 'DETAILED');

-- 5. 최적화 전후의 I/O 성능 비교
SET STATISTICS IO ON;

PRINT '--- 최적화 전 조회 ---';
SELECT * FROM FragTestTable WHERE ID BETWEEN 100 AND 500;

-- 인덱스 재구축 수행
ALTER INDEX CL_IDX_ID ON FragTestTable REBUILD;

PRINT '--- 최적화 후 조회 ---';
SELECT * FROM FragTestTable WHERE ID BETWEEN 100 AND 500;

SET STATISTICS IO OFF;

위 시뮬레이션을 실행하면 REBUILD 작업 이후 논리적 읽기(logical reads) 수가 현저히 감소하는 것을 확인할 수 있습니다. 인덱스 페이지가 연속적으로 정렬되면서 데이터를 읽어오는 효율이 극대화되었기 때문입니다. 주기적인 인덱스 관리는 대규모 데이터베이스 환경에서 필수적인 유지보수 항목입니다.