SQL Server Tuning - DB 테이블의 단편화 검사

SQL Server에서 특정 DB의 테이블에 저장된 데이터들의

단편화 상태를 확인하려면 다음과 같은 명령어를 사용한다.

USE DB명
GO
DBCC SHOWCONTIG
GO

예로 아래와 같은 형태로

리포트가 나타난다. 내용이 많고 모르는 것도 있지만 대충은 '검색 밀도' 부분을 보고 단편화가 얼마나 되어 있구나. 튜닝이 좀 필요하겠구나~

하는 정도는 알 수 있을 것이다.

DBCC SHOWCONTIG이(가) 'GST_Alert' 테이블을 검색하는 중...
테이블: 'GST_Alert'(178099675); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 2
- 검색한 익스텐트 ..............................: 2
- 익스텐트 스위치..............................: 1
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 50.00% [1:2]
- 논리 검색 조각화 상태 ..................: 50.00%
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 2931.0
- 평균 페이지 밀도(전체).....................: 63.79%
DBCC SHOWCONTIG이(가) 'GST_AlertError' 테이블을 검색하는 중...
테이블: 'GST_AlertError'(226099846); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_AlertHistory' 테이블을 검색하는 중...
테이블: 'GST_AlertHistory'(258099960); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 6
- 검색한 익스텐트 ..............................: 4
- 익스텐트 스위치..............................: 3
- 익스텐트당 평균 페이지 수........................: 1.5
- 검색 밀도[최적:실제].......: 25.00% [1:4]
- 익스텐트 검색 조각화 상태 ...................: 75.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 7170.2
- 평균 페이지 밀도(전체).....................: 11.41%
DBCC SHOWCONTIG이(가) 'GST_BLOG' 테이블을 검색하는 중...
테이블: 'GST_BLOG'(290100074); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_Alert_Key' 테이블을 검색하는 중...
테이블: 'GST_Alert_Key'(322100188); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC SHOWCONTIG이(가) 'GST_FILES' 테이블을 검색하는 중...
테이블: 'GST_FILES'(338100245); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 6474.0
- 평균 페이지 밀도(전체).....................: 20.01%
DBCC SHOWCONTIG이(가) 'TB_MEMO' 테이블을 검색하는 중...
테이블: 'TB_MEMO'(370100359); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 7930.0
- 평균 페이지 밀도(전체).....................: 2.03%
DBCC SHOWCONTIG이(가) 'GST_Receiver' 테이블을 검색하는 중...
테이블: 'GST_Receiver'(402100473); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 2
- 검색한 익스텐트 ..............................: 2
- 익스텐트 스위치..............................: 1
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 50.00% [1:2]
- 논리 검색 조각화 상태 ..................: 50.00%
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 804.0
- 평균 페이지 밀도(전체).....................: 90.07%
DBCC SHOWCONTIG이(가) 'GST_AlertStatus' 테이블을 검색하는 중...
테이블: 'GST_AlertStatus'(482100758); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 8059.0
- 평균 페이지 밀도(전체).....................: 0.43%
DBCC SHOWCONTIG이(가) 'GST_Sender' 테이블을 검색하는 중...
테이블: 'GST_Sender'(514100872); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 3
- 검색한 익스텐트 ..............................: 3
- 익스텐트 스위치..............................: 2
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 33.33% [1:3]
- 논리 검색 조각화 상태 ..................: 66.67%
- 익스텐트 검색 조각화 상태 ...................: 33.33%
- 페이지당 사용 가능한 평균 바이트 수.....................: 1363.0
- 평균 페이지 밀도(전체).....................: 83.16%
DBCC SHOWCONTIG이(가) 'GST_SMS_USER' 테이블을 검색하는 중...
테이블: 'GST_SMS_USER'(1317579732); 인덱스 ID: 0, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 15
- 검색한 익스텐트 ..............................: 4
- 익스텐트 스위치..............................: 3
- 익스텐트당 평균 페이지 수........................: 3.8
- 검색 밀도[최적:실제].......: 50.00% [2:4]
- 익스텐트 검색 조각화 상태 ...................: 50.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3540.4
- 평균 페이지 밀도(전체).....................: 56.26%
DBCC SHOWCONTIG이(가) 'GST_SMS_GROUP' 테이블을 검색하는 중...
테이블: 'GST_SMS_GROUP'(1381579960); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 1
- 검색한 익스텐트 ..............................: 1
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 1.0
- 검색 밀도[최적:실제].......: 100.00% [1:1]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3429.0
- 평균 페이지 밀도(전체).....................: 57.64%
DBCC SHOWCONTIG이(가) 'GST_UserSetting' 테이블을 검색하는 중...
테이블: 'GST_UserSetting'(1506104406); 인덱스 ID: 1, 데이터베이스 ID: 25
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 0
- 검색한 익스텐트 ..............................: 0
- 익스텐트 스위치..............................: 0
- 익스텐트당 평균 페이지 수........................: 0.0
- 검색 밀도[최적:실제].......: 100.00% [0:0]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 0.00%
- 페이지당 사용 가능한 평균 바이트 수.....................: 0.0
- 평균 페이지 밀도(전체).....................: 0.00%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

그러면 이러한 조각화는 어떻게 해결할까?

단편화의 처리는 Index Deflagmenting & Index rebuilding 작업으로 진행합니다.


일반적으로 DBCC 명령어를 통해 작업하고

DBCC DBREINDEX명령과 DBCC INDEXDEFLAG 명령으로 진행하죠.

아래와 같은 구문을 사용하면 손쉽게 테이블 전체에 인덱스 rebuilding 작업을 할 수 있습니다.

use DB명
go
exec dbo.sp_MSforeachtable 'dbcc dbreindex(''?'') with no_infomsgs'
go