[SQL] 인덱스 조각화

인덱스 조각화란?

데이터의 삽입, 업데이트, 삭제와 같은 작업이 반복되면서 인덱스 페이지들이 물리적으로 비효율적인 상태로 변하는 현상
이로 인해 데이터 검색 성능이 저하되고, 쿼리 실행 시간이 길어질 수 있다.

조각화는 클러스터형 인덱스와 비클러스터형 인덱스에서 발생할 수 있는데, 1) 내부 조각화 와 2) 외부 조각화가 있다.

 

---

 

인덱스 조각화 발생 원인

1. 데이터 삽입 : 새로운 데이터가 기존 페이지에 들어가지 못하고 새로운 페이지로 분산될 때.

2. 데이터 삭제 : 삭제된 공간이 재사용되지 않고, 빈 공간으로 남아있는 경우.

3. 데이터 업데이트 : 기존 데이터의 크기가 커져, 현재 페이지에 저장되지 못하고 다른 페이지로 이동할 때.

4. 인덱스 재구성 부족 : 인덱스 생성 후, 오랜 기간 동안 인덱스를 재구성하지 않으면 조각화가 누적됨.

 

---

 

인덱스 조각화의 유형

1. 내부 조각화 (Internal Fragmentation)

: 페이지 내부의 비효율성을 의미한다.

• 한 인덱스 페이지가 빈 공간으로 채워져 있는 경우 발생.
• 데이터가 삭제되거나 업데이트로 인해 행 크기가 줄어들면 페이지 안에 사용되지 않는 빈 공간이 생김.
• 더 많은 페이지를 읽어야 해서 쿼리 성능이 떨어짐.

 

2. 외부 조각화 (External Fragmentation)

: 인덱스 페이지들이 물리적으로 연속되지 않은 경우를 의미한다.

• 데이터가 논리적으로는 순서대로 정렬되어 있지만, 실제로는 디스크의 여러 위치에 분산되어 저장됨.
• 주로 데이터 삽입이나 행 크기가 변하는 업데이트 작업으로 인해 발생함.
• 디스크 읽기가 비효율적이고, 데이터 검색 속도가 느려짐.

 

---

 

성능 이슈 확인

1)특정 테이블, 인덱스의 페이지 정보 확인

--특정 테이블, 인덱스의 페이지 정보를 확인
DBCC SHOWCONTIG('테이블명', '인덱스명')

 

내가 생성한 테이블에는 아직 데이터가 없어서 전체적으로 0이 뜬다.

가장 위에 있는 검색한 페이지는 Leaf Level의 총 페이지 수를 의미한다.

논리 검색 조각화 상태를 보면 0인데, 이 부분이 조각화의 정도를 나타낸다.

가장 밑에 있는 평균 페이지 밀도(전체)는 각 페이지별로 데이터가 저장되는 정도를 의미하고, 높을수록 좋다.

 

2) SQL Server에서 전체적인 조각화 상태

SELECT 
    OBJECT_NAME(IPS.OBJECT_ID) AS TableName,
    I.Name AS IndexName,
    IPS.index_type_desc AS IndexType,
    IPS.avg_fragmentation_in_percent AS Fragmentation
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS IPS
JOIN 
    sys.indexes AS I
ON 
    IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
WHERE 
   -- IPS.avg_fragmentation_in_percent > 10;   -- 조각화 10% 이상의 값

 

0~10% : 정상

10~30% : 조각화를 개선하기 위해 인덱스 재구성(reorganize) 권장

30% 이상 : 인덱스 재작성(rebuild)를 수행하는게 좋음

 

---

 

인덱스 조각화 해결 방법

1) 인덱스 재구성 (Reorganize)

• 페이지를 다시 정렬하여 조각화를 줄이는 작업
• 비교적 빠르고, 서버의 다른 작업에 크게 영향을 주지 않음

ALTER INDEX 인덱스명 ON TableName REORGANIZE;

 

2) 인덱스 재작성 (Rebuild)

• 인덱스를 완전히 다시 작성하여 조각화를 제거
• 오래 걸리지만, 외부 조각화를 포함해 모든 조각화를 제거할 수 있음

ALTER INDEX 인덱스명ON TableName REBUILD;

 

* SQL Server Maintenance Plan을 통해 주기적으로 인덱스 재구성 및 재작성을 수행하도록 자동화 할 수 있다.

 

---

 

조각화 방지 방법

1) FILLFACTOR 조정

• 인덱스 페이지가 새 데이터를 삽입할 공간을 미리 확보하도록 설정함.
• 기본값은 100%지만, 조각화를 줄이기 위해 70~90% 정도로 설정 가능.

CREATE NONCLUSTERED INDEX IDX_IndexName
ON TableName (ColumnName)
WITH (FILLFACTOR = 80);

 

2) 정기적인 인덱스 유지관리

   : 주기적으로 인덱스를 재구성하거나 재작성하여 조각화를 최소화한다.

 

3) 적절한 인덱스 설계

• 자주 변경되는 데이터를 포함한 인덱스는 잘.. 설계
• 필요하지 않은 인덱스를 제거