MSSQL 인덱스 재구성 재구축으로 성능 향상하는 방법

📊 인덱스 조각화란 무엇인가

MSSQL에서 인덱스 조각화(Index Fragmentation)는 데이터 페이지의 물리적 순서와 논리적 순서가 일치하지 않는 현상을 말합니다.
즉, 데이터가 디스크나 메모리 상에서 불연속적으로 저장되어 검색 시 추가적인 읽기 작업이 발생하게 되는 것이죠.
이는 시간이 지남에 따라 데이터의 삽입, 수정, 삭제가 반복되면서 점점 심화됩니다.

조각화가 심해지면 쿼리 실행 계획에서 비효율적인 경로가 선택되거나, 디스크 I/O가 증가하여 응답 시간이 길어집니다.
특히 대용량 테이블이나 트랜잭션이 많은 환경에서는 성능 저하가 눈에 띄게 나타날 수 있습니다.
이 때문에 인덱스 조각화를 주기적으로 점검하고 적절히 관리하는 것이 매우 중요합니다.

🔍 조각화 유형

MSSQL에서 인덱스 조각화는 크게 논리적 조각화(Logical Fragmentation)와 물리적 조각화(Extent Fragmentation)로 나뉩니다.
논리적 조각화는 인덱스 페이지 순서가 뒤섞이는 현상이고, 물리적 조각화는 디스크 상에서 데이터 블록이 흩어져 저장되는 현상을 의미합니다.

📏 조각화율 확인 방법

조각화율을 확인하려면 다음과 같이 sys.dm_db_index_physical_stats DMV를 활용할 수 있습니다.

SELECT 
    dbschemas.[name] as '스키마명',
    dbtables.[name] as '테이블명',
    dbindexes.[name] as '인덱스명',
    indexstats.avg_fragmentation_in_percent as '조각화율'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id;

일반적으로 조각화율이 5% 미만이면 조치가 필요 없으며, 5~30% 사이라면 REORGANIZE, 30% 이상이라면 REBUILD를 권장합니다.

🛠️ ALTER INDEX REBUILD 특징과 활용

ALTER INDEX REBUILD 명령은 기존 인덱스를 완전히 삭제한 후 새로 생성하는 방식으로 동작합니다.
이 과정에서 데이터 페이지들이 물리적, 논리적으로 재배치되므로 조각화를 완벽하게 제거할 수 있습니다.
다만, 이 작업은 상당한 리소스를 소모하며 테이블이나 인덱스에 잠금이 걸리기 때문에 대규모 테이블에서는 신중히 계획해야 합니다.

⚡ 주요 장점

• ✅조각화를 완전히 제거하여 인덱스를 최적 상태로 복구
• 🚀페이지 밀도를 최적화해 검색 속도 향상
• 🔄병렬 처리가 가능하여 멀티코어 환경에서 효율적

⚠️ 주의사항

💻 실행 예시

ALTER INDEX 인덱스명
ON 스키마명.테이블명
REBUILD WITH (ONLINE = ON);

위 예시는 온라인 상태에서 인덱스를 재구성하는 방법입니다.
만약 온라인 옵션을 생략하면 작업 도중 인덱스가 잠기게 됩니다.
따라서 운영 환경에서는 가능하면 온라인 옵션을 사용하는 것이 좋습니다.

⚙️ ALTER INDEX REORGANIZE 특징과 활용

ALTER INDEX REORGANIZE 명령은 인덱스 페이지를 물리적으로 재정렬하고, 페이지 내부의 빈 공간을 최적화하는 작업입니다.
이는 조각화율이 비교적 낮을 때 사용되며, 인덱스를 완전히 재생성하지 않으므로 부하와 잠금이 적습니다.
운영 중인 데이터베이스에서도 서비스 중단 없이 수행할 수 있다는 점이 가장 큰 장점입니다.

🔹 주요 장점

• 💡온라인 환경에서 서비스 중단 없이 실행 가능
• 📉리소스 사용량이 적어 대규모 테이블에도 부담이 적음
• 🧩인덱스 내부 페이지를 재배치해 데이터 접근 효율 향상

⚠️ 주의사항

💻 실행 예시

ALTER INDEX 인덱스명
ON 스키마명.테이블명
REORGANIZE;

위 명령은 해당 인덱스의 조각화를 줄이고 페이지 밀도를 최적화합니다.
특히 24시간 가동되는 서비스에서는 REORGANIZE 방식이 안정적인 선택이 될 수 있습니다.

📌 조각화율에 따른 선택 기준

인덱스 조각화를 줄이는 방법을 선택할 때는 조각화율이 핵심 판단 기준이 됩니다.
조각화율은 데이터 페이지의 불연속성을 수치로 표현한 값으로, 이 비율에 따라 REBUILD와 REORGANIZE 중 어떤 방법을 사용할지 결정할 수 있습니다.

📊 선택 기준 표

💡 운영 환경 적용 팁

이러한 기준을 적용하면 불필요한 리소스 낭비를 줄이고, 효율적으로 인덱스를 관리할 수 있습니다.
특히, 자동화 스크립트나 유지보수 계획과 함께 적용하면 장기적인 성능 유지에 큰 도움이 됩니다.

💡 인덱스 유지관리 자동화 팁

MSSQL의 인덱스 조각화 관리는 주기적으로 실행해야 효과를 볼 수 있습니다.
이를 위해 수동으로 매번 실행하는 대신, 자동화된 작업 스케줄을 설정하면 효율적이고 안정적인 성능 유지가 가능합니다.
자동화는 SQL Server Agent 작업, 유지관리 계획(Maintenance Plan), 또는 PowerShell 스크립트를 활용해 구현할 수 있습니다.

🛠️ SQL Server Agent 활용

SQL Server Agent를 사용하면 지정한 시간대에 자동으로 REBUILD 또는 REORGANIZE 명령을 실행하도록 설정할 수 있습니다.
아래 예시는 조각화율에 따라 자동으로 명령을 선택하는 스크립트 예시입니다.

DECLARE @TableName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)
DECLARE @Frag FLOAT

DECLARE index_cursor CURSOR FOR
SELECT 
    t.name, i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL

OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Frag

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Frag > 30
        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD')
    ELSE IF @Frag > 5
        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE')

    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Frag
END

CLOSE index_cursor
DEALLOCATE index_cursor

⚙️ 유지관리 계획(Maintenance Plan)

SQL Server Management Studio(SSMS)에서 제공하는 유지관리 계획 마법사를 이용하면 GUI 환경에서 손쉽게 인덱스 유지관리 작업을 예약할 수 있습니다.
특히 초보 관리자도 복잡한 스크립트 작성 없이 일정에 맞춰 자동으로 조각화를 관리할 수 있다는 장점이 있습니다.

📌 PowerShell 스크립트 활용

대규모 환경이나 여러 서버를 동시에 관리하는 경우 PowerShell 스크립트를 이용해 인덱스 최적화 작업을 중앙에서 제어할 수 있습니다.
이 방식은 서버별 환경에 맞게 유연한 설정이 가능하다는 장점이 있습니다.

❓ 자주 묻는 질문 (FAQ)