인덱스 조각화 확인

인덱스 조각화 정보 확인 (DBCC SHOWCONTIG)
- 정기 적인 조각화 정보 확인을 통한 인덱스 재구성 주기 결정
- 적절한 채우기 비율 (fillfactor) 결정

- DBCC SHOWCONTIG('테이블명', '인덱스명')

 

[T_FLOWART 테이블]

DBCC SHOWCONTIG('T_FLOWART', 'PK_T_FLOWART')

 

(결과 출력)

DBCC SHOWCONTIG이(가) 'T_FLOWART' 테이블을 검색하는 중...
테이블: 'T_FLOWART'(423672557); 인덱스 ID: 1, 데이터베이스 ID: 5
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 33649 (현재 33649 페이지를 사용하고 있다)
- 검색한 익스텐트 ..............................: 4231 (사용하는 익스텐트)
- 익스텐트 스위치..............................: 10337

최적화 익스텐트 스위치 갯수는 검색한 익스텐트 - 1 한 결과 값이어야 한다.

즉, 4231 - 1 = 4230 이어야지 최적화되었다고 말할수 있다.

- 익스텐트당 평균 페이지 수........................: 8.0
- 검색 밀도[최적:실제].......: 40.69% [4207:10338]

Best Count 대 Actual Count의 비율입니다.

모든 데이터가 인접한 경우 이 값은 100이고 이 값이 100보다 작으면 일부 데이터가 조각화된

것입니다.

사용할 익스텐트의 갯수가 4207이면 충분한데 10338 갯수를 사용하고

있다라는 뜻이다. 즉, 조각화가 많이 발생되어 있다라는 말이다.(리소스 낭비)

 

- 논리 검색 조각화 상태 ..................: 20.97%
- 익스텐트 검색 조각화 상태 ...................: 0.57%
- 페이지당 사용 가능한 평균 바이트 수.....................: 935.5
- 평균 페이지 밀도(전체).....................: 88.44%

평균 페이지 밀도입니다(백분율). 이 값은 행 크기의 영향을 받습니다. 따라서 이 값은 페이지의 꽉 찬 정도를 보다 정확하게 반영합니다. 이 백분율 값이 클수록 좋습니다.

 

 

 

[인덱스 재구성 3가지 방법]
WITH DROP_EXISTING
- 페이지 압축
- 컬럼 변경
- FILLFACTOR, PAD_INDEX 변경
- 비클러스터형 인덱스를 클러스터형 인덱스로  변경
DBCC DBREINDEX
- 페이지 압축
- FILLFACTOR 변경
- 테이블의 모든 인덱스 일괄 적용 가능
DBCC INDEXDEFRAG
- 물리적인 리프 페이지의 순서를 논리적인 순서로 재정렬
- 기존에 지정된 FILLFACTOR 적용

 

 

[인덱스 재구성 구문]

WITH DROP_EXISTING

CREATE CLUSTERED INDEX IDX_EMP_01
ON EMP (DEPTNO ASC, TITLE DESC, SAL DESC )
WITH DROP_EXISTING
ON HR_FILEGROUP

DBCC DBREINDEX
DBCC DBREINDEX ('테이블명','인덱스명', 80)

DBCC INDEXDEFRAG

DBCC  INDEXDEFRAG ('DB명','테이블명','인덱스명')

 

 

[SQL Server 2005에서 인덱스 조각화 정보 확인 방법]

 

[구문]

sys.dm_db_index_physical_stats (

    { database_id | NULL }               /* NULL: 서버내의 모든 데이터베이스 */

    , { object_id | NULL }                /* NULL: 해당 데이터베이스내의 모든 오브젝트 */

    , { index_id | NULL | 0 }              /* NULL: 해당 테이블의 모든 인덱스 */

    , { partition_number | NULL } /* NULL: 해당 오브젝트의 모든 인덱스 */

    , { mode | NULL | DEFAULT } /* NULL: LIMIT */

)

 

select * from sys.dm_db_index_physical_stats (db_id('test'), object_id('t3'), null, null, null)

위의 쿼리를 통해 test 데이터베이스의 t3 테이블에 있는 모든 인덱스의 조각화 정보를 살펴볼 수 있다.

 

avg_fragmentation_in_percent 컬럼의 값이 조각화를 의미하는데,

이 조각화의 수치가 올라갈수록 성능에 나쁜 영향을 미칠 수 있다.