[MSSQL] INDEX REBUILD 및 조각화 진단

모든 DB가 공통적으로 DML(UPDATE, DELETE, INSERT) 작업이 많이 발생하면 인덱스 조각화가 발생하며, DB 성능이 떨어지고 비효율적으로 공간을 사용하게 된다. 그래서 인덱스 조각화에 따라 INDEX REBUILD 를 주기적으로 해주어야 한다. (enterprise가 아니라면.. online 중 rebuild 가 안되기 때문에 운영중에는.. 하기 쉽지 않다..) online 중으로 하게 되더라도 보통 운영 중인 DB는 부하가 갈 수 있고, 소요시간도 오래걸리기 때문에 사용량이 적은 시간에 진행해야 한다.

 

※ 인덱스 조각화 진단

-- 특정 TABLE 진단
SELECT 
    dbschemas.name AS SchemaName,
    dbtables.name AS TableName,
    dbindexes.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('스키마.테이블_이름'), NULL, NULL, 'LIMITED') AS indexstats
    JOIN sys.indexes AS dbindexes ON indexstats.object_id = dbindexes.object_id AND indexstats.index_id = dbindexes.index_id
    JOIN sys.tables AS dbtables ON dbindexes.object_id = dbtables.object_id
    JOIN sys.schemas AS dbschemas ON dbtables.schema_id = dbschemas.schema_id
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC;

-- 전체 DB 진단
SELECT 
    dbschemas.name AS SchemaName,
    dbtables.name AS TableName,
    dbindexes.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
    JOIN sys.indexes AS dbindexes ON indexstats.object_id = dbindexes.object_id AND indexstats.index_id = dbindexes.index_id
    JOIN sys.tables AS dbtables ON dbindexes.object_id = dbtables.object_id
    JOIN sys.schemas AS dbschemas ON dbtables.schema_id = dbschemas.schema_id
WHERE 
    dbindexes.index_id > 0 -- 인덱스가 있는 경우
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC;

 

     ※ REBUILD 기준은 운영 중인 DB 마다 다름

• 10% ~ 30% 조각화가 있는 경우에는 인덱스를 Reorganize하여 처리하는 것이 일반적.
• Reorganize는 시스템 리소스를 적게 사용하고, 온라인에서 실행 가능

 

인덱스 Rebuild 과정

인덱스를 재구성하는 방법: 온라인(Online)과 오프라인(Offline)

온라인 모드에서는 테이블이 잠기지 않아도 되지만, 오프라인 모드에서는 테이블에 LOCK이 발생한다.

1. 인덱스 용량 산정

-- 데이터베이스 전체 공간 사용량 확인
EXEC sp_spaceused;

-- 특정 테이블의 공간 사용량 확인
EXEC sp_spaceused '[스키마].[테이블_이름]';

2. 특정 테이블의 인덱스를 재구성하는 SQL

-- 오프라인 인덱스 Rebuild
ALTER INDEX [인덱스_이름] ON [스키마].[테이블_이름] REBUILD;

-- 모든 인덱스 오프라인 Rebuild
ALTER INDEX ALL ON [스키마].[테이블_이름] REBUILD;

-- 온라인 인덱스 Rebuild (Enterprise Edition에서만 가능)
ALTER INDEX [IndexName] ON [TableName]
REBUILD
WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, ONLINE = ON, STATISTICS_NORECOMPUTE = ON, MAXDOP = 4);


-- 모든 인덱스 온라인 Rebuild
ALTER INDEX ALL ON [TableName]
REBUILD
WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = OFF, ONLINE = OFF, STATISTICS_NORECOMPUTE = OFF, MAXDOP = 2);

 

 

• FILLFACTOR = 80: 페이지에 데이터를 채울 때 80%만 채움.
• SORT_IN_TEMPDB = ON: 정렬 작업을 TempDB에서 수행.
• ONLINE = ON: 인덱스 리빌드를 온라인으로 실행 (테이블 잠금을 최소화).
• STATISTICS_NORECOMPUTE = ON: 리빌드 중에 통계를 다시 계산하지 않음.
• MAXDOP = 4: 병렬 처리를 4개의 CPU로 제한.

 

 

 

※ REBUILD 하면서 LOG 파일 용량 산정 후 LOG 백업 수행

-- 로그 파일의 사용률 확인
DBCC SQLPERF(LOGSPACE);

LOG 백업을 돌려주지 않으면 자동증설로 되어 있을 경우 로그 파일이 계속 커진다...
로그파일은 한번 커지면 줄이기 쉽지 않기 때문에 주의해서 관리 필요

-- 로그 백업
BACKUP LOG [데이터베이스_이름]
TO DISK = 'C:\Backup\LogBackup.bak'
WITH INIT;