[MSSQL] INDEX 파편화 확인 인덱스 리빌드를 통한 성능 최적화

 

데이터베이스 성능을 유지하고 최적화하는 과정에서 인덱스 파편화는 무시할 수 없는 요소입니다. 특히, 삽입과 업데이트가 빈번하게 이루어지는 테이블에서는 시간이 지남에 따라 인덱스 페이지가 불필요하게 나뉘거나, 효율적으로 정렬되지 않음으로 인해 성능 저하가 발생할 수 있습니다.

 

1. 인덱스 파편화란?

인덱스 파편화는 인덱스 페이지가 물리적으로 분산되어 데이터 검색 성능을 저하시키는 현상을 말합니다. 데이터가 추가되거나 삭제되면 인덱스 페이지가 비효율적으로 나뉘어져 디스크 I/O 작업이 증가하게 되고, 이로 인해 쿼리 성능이 떨어지게 됩니다. 특히 INSERT, UPDATE 작업이 많은 테이블에서 이러한 문제가 자주 발생합니다.

 

2. 인덱스 파편화 확인 방법

SQL Server에서는 sys.dm_db_index_physical_stats 뷰를 사용하여 테이블의 인덱스 파편화 정도를 확인할 수 있습니다. 다음은 특정 테이블에서 인덱스 파편화 상태를 확인하는 쿼리 예시입니다.

 

-- 파편화가 30% 이상 진행된 테이블 조회
SELECT
    OBJECT_NAME(a.object_id) AS [Table Name],
    b.name AS [Index Name],
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30
ORDER BY avg_fragmentation_in_percent desc

-- 특정 테이블 파편화 조회 
SELECT 
    index_id, 
    index_type_desc, 
    avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('[테이블명]'), NULL, NULL, 'DETAILED');

 

avg_fragmentation_in_percent 값이 30% 이상이면 인덱스 파편화가 심각한 상태라고 볼 수 있으며, 이 경우 인덱스를 재구성하여 성능을 개선할 수 있습니다.

 

3. 인덱스 Rebuild vs Reorganize

인덱스 파편화를 해결하기 위해 SQL Server에서는 **인덱스 재구성(Rebuild)**과 인덱스 재조정(Reorganize) 두 가지 방법을 제공합니다.

• 인덱스 재구성 (Rebuild): 기존 인덱스를 삭제하고 새로운 인덱스를 생성하는 방식입니다. 파편화된 인덱스를 완전히 재정렬하고, 통계를 함께 업데이트하므로 성능 개선 효과가 큽니다.
• 인덱스 재조정 (Reorganize): 기존 인덱스의 페이지를 물리적으로 재정렬하는 방식입니다. 리빌드에 비해 부담이 적지만, 파편화가 심각한 경우에는 효과가 미미할 수 있습니다.

4. 인덱스 리빌드를 통한 성능 개선

인덱스 재구성은 특히 30% 이상의 파편화가 발생한 경우에 추천됩니다. 파편화가 발생한 테이블에서 인덱스를 재구성하면 데이터 페이지가 다시 정렬되어 쿼리 성능이 크게 향상됩니다. 다음은 특정 테이블에 대해 인덱스를 재구성하는 SQL 명령어입니다.

ALTER INDEX ALL ON [테이블명]
REBUILD WITH (FILLFACTOR = 90);

 

이 명령어는 모든 인덱스를 재구성하며, 인덱스 페이지의 90%만 채우고 10%는 여유 공간으로 남겨둡니다. 이를 통해 추가적인 데이터 삽입 시 발생할 수 있는 **페이지 분할(page split)**을 줄일 수 있습니다.

 

5. FILLFACTOR 설정의 중요성

FILLFACTOR는 인덱스 페이지를 어느 정도까지 채울지 결정하는 중요한 옵션입니다. 만약 INSERT 작업이 빈번한 테이블이라면, FILLFACTOR를 100%로 설정할 경우 페이지가 가득 차서 자주 분할이 발생하게 됩니다. 반면, FILLFACTOR 90으로 설정하면 인덱스 페이지에 여유 공간을 남겨두어 페이지 분할을 줄이고 성능을 향상시킬 수 있습니다.

 

그러나, FILLFACTOR를 낮게 설정하는 것이 항상 좋은 것은 아닙니다. 다음과 같은 상황에서는 FILLFACTOR 90이 적합하지 않을 수 있습니다.

 

FILLFACTOR 90이 안 좋은 경우:

1. 읽기 성능이 매우 중요한 경우: SELECT 쿼리가 대규모로 자주 실행되고, 읽기 성능이 중요할 때는 FILLFACTOR가 낮으면 읽기 성능이 저하될 수 있습니다. 특히 읽기 중심 시스템에서는 페이지 분할보다는 디스크 I/O의 최소화가 중요하므로, **FILLFACTOR를 100%**로 유지하는 것이 더 나을 수 있습니다.
2. 데이터가 거의 변하지 않는 테이블: 변경 작업(INSERT, UPDATE)이 거의 없는 테이블에서는 FILLFACTOR를 낮추는 것이 불필요하며, 오히려 불필요한 디스크 공간 낭비로 이어질 수 있습니다. 이런 테이블은 FILLFACTOR = 100을 사용하는 것이 일반적입니다.
3. 디스크 공간이 중요한 경우: 낮은 FILLFACTOR를 설정하면 인덱스 페이지가 여유 공간을 차지하게 되어 더 많은 디스크 공간을 사용합니다. 만약 디스크 공간이 제한적이라면, 낮은 FILLFACTOR는 부적절할 수 있습니다.

-- 테이블의 FILLFACTOR 확인쿼리
SELECT *
FROM sys.indexes 
WHERE object_id = OBJECT_ID('[테이블명]');

 

6. 인덱스 리빌드로 얻을 수 있는 성능 향상

인덱스 리빌드를 통해 얻을 수 있는 성능 향상은 크게 두 가지로 나뉩니다:

• 쿼리 성능 향상: 파편화된 인덱스를 재구성하여 데이터 페이지가 물리적으로 연속되게 정렬됨으로써, SQL Server가 데이터를 읽고 쓰는 속도가 빨라집니다.
• 쓰기 성능 최적화: 적절한 FILLFACTOR 설정으로 삽입/업데이트 시 발생하는 페이지 분할을 줄여, 쓰기 성능이 향상됩니다.

7. 정기적인 인덱스 관리의 필요성

인덱스는 데이터베이스의 성능을 크게 좌우하는 중요한 요소입니다. 특히, 대규모 트랜잭션이 빈번한 환경에서는 정기적인 인덱스 관리를 통해 데이터베이스 성능을 안정적으로 유지할 수 있습니다. 정기적으로 인덱스의 파편화 상태를 확인하고, 필요한 경우 인덱스를 재구성하는 것이 좋습니다.