인덱스 조각화

1. 테스트 용으로 계좌테이블에서 샘플 데이터를 1000개 만큼 m2에 붇는다.

INSERT m2

  SELECT top 1000 * FROM taccountmst

2. 클러스터 인덱스를 생성하고

CREATE CLUSTERED INDEX cx

  ON m2 (userno)

GO

 
3. m2 테이블의 인덱스 조각상태를 확인한다.
 
DBCC SHOWCONTIG(m2, cx)

/*

DBCC SHOWCONTIG이(가) 'm2' 테이블을스캔하는중...

테이블: 'm2' (1128443144); 인덱스ID: 1, 데이터베이스ID: 7

TABLE 수준스캔이수행되었습니다.

- 스캔한페이지................................: 13

- 스캔한익스텐트..............................: 2

- 전환된익스텐트..............................: 1

- 익스텐트당평균페이지수........................: 6.5

- 스캔밀도[최적:실제].......: 100.00% [2:2]

- 논리스캔조각화상태..................: 0.00%

- 익스텐트스캔조각화상태...................: 50.00%

- 페이지당사용가능한평균바이트수.....................: 268.8

- 평균페이지밀도(전체).....................: 96.68%

DBCC 실행이완료되었습니다. DBCC에서오류메시지를출력하면시스템관리자에게문의하십시오.

*/

4. 임의로 균등하게 유저 번호를 insert 시킨다.
 INSERT m2

  SELECT top 1000* FROM taccountmst

    WHERE userno % 40 = 0

DBCC SHOWCONTIG(m2, cx)

/*

DBCC SHOWCONTIG이(가) 'm2' 테이블을스캔하는중...

테이블: 'm2' (1128443144); 인덱스ID: 1, 데이터베이스ID: 7

TABLE 수준스캔이수행되었습니다.

- 스캔한페이지................................: 37

- 스캔한익스텐트..............................: 6

- 전환된익스텐트..............................: 24

- 익스텐트당평균페이지수........................: 6.2

- 스캔밀도[최적:실제].......: 20.00% [5:25]

- 논리스캔조각화상태..................: 32.43%

- 익스텐트스캔조각화상태...................: 66.67%

- 페이지당사용가능한평균바이트수.....................: 2583.4

- 평균페이지밀도(전체).....................: 68.08%

DBCC 실행이완료되었습니다. DBCC에서오류메시지를출력하면시스템관리자에게문의하십시오
* /

Insert된 데이타들은 거의 대부분 인덱스 조각화를 발생 시켰으며 평균 페이지 밀도는 68%로 떨어져
성능 저하를 가져 올수 있는 여지가 있다.

그러면 인덱스 조각은 왜 발생할까?

클러스터 인덱스가 다음과 같이 생성되어 있다고 하자

 이때 ST3라는 새로운 값이 들어온다면 클러스터 인덱스에 변화가 생긴다.
 

 a. ST3가 들어가야 할 101 페이지는 이미 꽉 차있기 때문에 Page Split이 일어난다.

 b. 101에 있던 데이터 절반인 ST2, TS 데이터가 104페이지로 새로 생성된다.

 c. ST3는 104 페이지의 2번째 자리에 insert 된다.
 
 d. root 페이지에 ST2에 해당하는 인덱스가 갱신된다.

이처럼 리프 데이터 페이지에 이미 꽉 차있을 경우 Page Split이 일어 나게 되며, 이를 인덱스 조각화라 한다.

처음 인덱스를 생성하게 되면 각 페이지들은 데이터로 모두 채워지게 되는데,

데이터가 추가 될때 마다 Split 발생 가능성이 높고 인덱스 조각화는 심해지게 된다.

그래서 이를 피하기 위해 인덱스 생성시, 가득찬 페이지를 만들지 않고 여유공간을 만드는
FILLFACTOR를 사용해도 된다.

CREATE INDEX... WITH PAD_INDEX, FILLFACTOR = 70


그리고 조각난 인덱스는 DBREINDEX 명령어로 다시 재구성 할 수 있습니다.

DBCC DBREINDEX(m2, '', 100) 

주의 : 인덱스를 생성하는 것과 같은 부하이기 때문에 실서비스 상황에서는 사용해서는 안됩니다.