테이블 사용량이 잦고 데이타가 많은 경우 프로시저의 이상은 없어보이는데도 데드락 현상이 발생하는 수가 있다.
테이블 검색 속도 자체가 너무 느려져서 생기는 문제인데 인덱스도 제대로 걸려 있는 상황이라면 단편화를 생각해 볼 필요가 있다.
먼저 단편화 정보를 확인하는 명령어는 DBCC SHOWCONTIG 이다.
다음은 mail_box 라는 테이블에 대한 단편화 정보를 조회해본 샘플이다.
참고로 이 테이블은 본인이 다니는 회사 제품 중에 메일 서비스에서 사용하는 테이블이다. 메일 사용량이 많은 경우 수십만건의 데이타가 존재하기도 하고 편지함 이동 및 삭제 등으로 프로그램에서의 입력, 수정, 삭제 작업이 빈번하게 일어나는 테이블이다.
DBCC SHOWCONTIG이(가) 'mail_box' 테이블을 검색하는 중...
테이블: 'mail_box'(1139691308); 인덱스 ID: 1, 데이터베이스 ID: 9
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 77802
- 검색한 익스텐트 ..............................: 10259
- 익스텐트 스위치..............................: 76211
- 익스텐트당 평균 페이지 수........................: 7.6
- 검색 밀도[최적:실제].......: 12.76% [9726:76212]
- 논리 검색 조각화 상태 ..................: 99.27%
- 익스텐트 검색 조각화 상태 ...................: 86.36%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3838.4
- 평균 페이지 밀도(전체).....................: 52.58%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
이때 속도에 영향을 미치는 부분은 익스텐트 스위치이다.
관련 용어를 설명해보자면 1페이지는 8Kbyte 를 갖는 공간이다. MSSQL DB 베이스를 구성하는 최소 단위라고 보면 된다. 1익스텐트는 8개의 페이지의 묶음이다. 고로 1 익스텐트는 8*8 = 64Kbyte 의 공간이다. 이건 뭐 -_- 의미없다. 구구단을 외자-_-)
이때 제일 중요한 수치인 익스텐트 스위치는 DBCC 문이 테이블이나 인덱스의 페이지를 이동하는 동안 익스텐트 간에 이동한 횟수를 나타낸다.
익스텐트 스위치의 값은 검색한 익스텐트의 값에 가능한 근접해야 한다. 그 말은 순서대로 정렬이 되어서 필요 이상의 이동을 하지 않았다는 의미가 된다. 반면에 단편화가 많이 되어 익스텐트 간의 이동한 횟수가 높아진다면 접근 속도에 영향을 준다.
이 비율은 검색 밀도 값으로 계산되며 값은 높을수록 좋으며 인덱스 조각화를 줄여 개선 가능하다.
위의 예에 나온 테이블은 보다시피 단편화 정도가 심각하여 시스템에 아주 많은 과부하를 가져다주게 되었다.
- 검색한 익스텐트 ..............................: 10259
- 익스텐트 스위치..............................: 76211
위에서 보다시피 익스텐트의 수보다 스위치 수가 7배 이상 많다. 그만큼 익스텐트의 순서가 꼬여 있다는 말이다.
이는 밀도에서도 바로 알 수 있다.
- 검색 밀도[최적:실제].......: 12.76% [9726:76212]
최적과 비교했을때는 거의 8배의 차이가 난다.
이런 단편화가 생기는 이유는 해당 테이블에 대한 입력, 삭제 작업이 워낙 잦은 경우 빈번하게 발생할 수 있는 문제이다.
주기적인 단편화 정리 작업을 자동으로 하도록 설정이 필요한 경우라 볼 수 있다.
참고로 단편화 정리 작업을 한 경우에는 아래의 수치로 변경이 되었다.
(그래봐야 페이지 재정렬이다 -_-)
DBCC SHOWCONTIG이(가) 'mail_box' 테이블을 검색하는 중...
테이블: 'mail_box'(1139691308); 인덱스 ID: 1, 데이터베이스 ID: 9
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 50463
- 검색한 익스텐트 ..............................: 6414
- 익스텐트 스위치..............................: 6670
- 익스텐트당 평균 페이지 수........................: 7.9
- 검색 밀도[최적:실제].......: 94.56% [6308:6671]
- 논리 검색 조각화 상태 ..................: 1.90%
- 익스텐트 검색 조각화 상태 ...................: 88.29%
- 페이지당 사용 가능한 평균 바이트 수.....................: 1519.7
- 평균 페이지 밀도(전체).....................: 81.22%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
보다시피 익스텐트 수와 익스텐트 스위치의 수가 거의 1:1에 가깝도록 변경되었으며 당연하겠지만 밀도도 94퍼센트로 올라갔다.
물론 전체를 새로 끼워 맞추는 작업도 가능하지만 운용중인 디비에 그러한 방식의 적용은 현실적으로 어렵다는걸 잊지 말자.
자 이런 경우 단편화 정리 (조각 모음)을 하는 명령어는 다음과 같다.
dbcc indexdefrag(db이름, "소유자.테이블이름",인덱스이름)
상세 정보는 MSDN 을 참고~
좋은 정보 감사합니다.
답글삭제