MSSQL은 데이터를 저장할 때 페이지에 저장한다. 페이지는 오라클의 블록과 같은 개념이다. 1페이지는 8K이며 8페이지가 1익스텐트이다. 그래서 MSSQL DB 서버는 포맷을 할 때에도 8K 단위로 포맷을 보통 하며 이는 페이지 크기와 일치시켜 성능 향상을 도모하는 것이다.
데이터를 자주 삽입, 삭제, 갱신을 하다보면 페이지에서 데이터가 들어갔다, 나왔다 하기 때문에 조각화가 일어날 수 밖에 없다. 따라서 조각화를 확인하고 이를 해결하는 방법을 설명하고자 한다.
MSSQL에서 DBCC SHOWCONTIG를 이용한 TABLE A의 인덱스 조각화 확인하는 방법이다.
DBCC SHOWCONTIG('TABLE_A', 'CIX__TABLE_A') 를 날리면 조각화 내용을 확인 할 수 있다.
DBCC SHOWCONTIG이(가) 'TABLE_A' 테이블을 검색하는 중...
다음과 같은 메시지가 출력이 되었다.
테이블: 'TABLE_A'(324442663); 인덱스 ID: 1, 데이터베이스 ID: 8
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 16865171
- 검색한 익스텐트 ..............................: 2197198
- 익스텐트 스위치..............................: 10178175
- 익스텐트당 평균 페이지 수........................: 7.7
- 검색 밀도[최적:실제].......: 20.71% [2108147:10178176]
- 논리 검색 조각화 상태 ..................: 54.92%
- 익스텐트 검색 조각화 상태 ...................: 31.40%
- 페이지당 사용 가능한 평균 바이트 수.....................: 2578.2
- 평균 페이지 밀도(전체).....................: 68.15%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
그렇다면 이 결과를 해석을 해보자.
검색한 페이지 - 1페이지가 8K이므로 스캔한 페이지는 16865171*8K이다.
검색한 익스텐트 - 1익스텐트가 8페이지이므로 2197198*8*8K이다.
익스텐트 스위치 - 익스텐트를 스캔하면서 익스텐트와 익스텐트 사이를 옮겨간 횟수를 의미한다.
익스텐트당 평균 페이지 수 - 페이지 체인에서 익스텐트 당 페이지 수를 의미한다. 하나의 익스텐트가 8개의 페이지인데 익스텐트에 조각화가 일어나서 하나의 익스텐트에 평균 7.7개의 페이지를 갖고 있다는 의미이다.
검색밀도[최적:실제] - 모든 데이터가 인접한 경우 이 값은 100이고 이 값이 100보다 작으면 일부 데이터가 조각화 된 것이다. 현재 20.71%이므로 조각화가 80%정도 된다는 이야기이다.
논리 검색 조각화 상태 - 인덱스의 리프 페이지 검색에서 반환된 순서가 잘못된 페이지의 비율이다. 힘과는 관계가 없으며 인덱스에 할당된 다음 물리적 페이지가 현재 리프 페이지의 다음 페이지 포인터가 가리키는 페이지와 다른 경우를 나타낸다. 즉 물리적 페이지가 현재 리프 페 삽입, 삭제, 갱신 등의 DML문에 의해서 인덱스의 트리 구조가 바뀐 것을 의미한다.
익스텐트 검색 조각화 상태 - 익스텐트의 리프 페이지 스캔에서 순서가 바뀐 익스텐트의 비율이다. 힙과는 관계가 없다. 순서가 잘못된 익스텐트란 인덱스의 현재 페이지가 들어 있는 익스텐트가 실제로 인덱스의 이전 페이지가 들어 있는 익스텐트의 다음 익스텐트가 아닌 경우이다. 인덱스가 여러 파일에 걸쳐 있을 경우 이 값은 의미가 없다.
페이지당 사용 가능한 평균 바이트 수 - 하나의 페이지는 최대 8060바이트를 저장할 수 있다. 현재 2578.2바이트를 사용가능하다는 이야기이다. 이 값이 클수록 페이지의 사용률이 낮다는 것이고 인덱스의 임의 삽입 횟수가 적은 경우 이 값이 작은 것이 좋다. 이 값은 행의 크기에 따라 달라지며 행의 킈기가 크면 값이 커진다.
평균 페이지 밀도(전체) - 평균 페이지의 밀도로 행의 크기의 영향을 받는다. 이 값은 페이지가 꽉 차있는 정도를 반영하며 클수록 좋다. 즉 스캔한 페이지의 조각화 상태를 의미하며 100%이면 조각난 상태가 아니고 현재 68.15%이므로 조각화가 일어났다는 의미이다.
이런 경우 테이블을 리빌드, 혹은 인덱스 리빌드를 해주면 된다.
인덱스를 리빌드 하는 방법은 다음 방법 중 하나를 쓰면 된다.
1. LOCK를 유발하는 REBUILD
- DBCC DBREINDEX(TABLE_NM,채우기비율)
- ALTER INDEX ALL ON TABLE_NM REBUILD WITH(여러 옵션들)
- ALTER INDEX INDEX_NM ON TABLE_NM REBUILD WITH(여러 옵션들)
2. LOCK을 발생시키지 않는 REBUILD
- DBCC INDEXDEFRAG(DB_NM, TABLE_A, INDEX_NM)
- ALTER INDEX ALL ON TABLE_NM REBUILD WITH(ONLINE=ON)
- ALTER INDEX INDEX_NM ON TABLE_NM REBUILD WITH(ONLINE=ON)
ONLINE=ON
인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 내재된 공유(IS) 잠금만 원본 테이블에 유지됩니다. 따라서 기본 테이블 및 인덱스를 계속 쿼리 또는 업데이트할 수 있습니다. 작업이 시작될 때 아주 짧은 기간 동안 S(공유) 잠금이 원본 개체에서 유지됩니다. 작업이 끝날 때 짧은 기간 동안 비클러스터형 인덱스가 생성되는 경우에는 원본에 대해 S(공유) 잠금이 유지되고, 온라인 상태에서 클러스터형 인덱스가 생성 또는 삭제될 때나 클러스터형 또는 비클러스터형 인덱스가 다시 작성될 때는 SCH-M(스키마 수정) 잠금이 획득됩니다. 로컬 임시 테이블에서 인덱스를 생성하는 경우에는 ONLINE을 ON으로 설정할 수 없습니다.
ONLINE=OFF
인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형 인덱스, 공간 인덱스 또는 XML 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업은 테이블에 대해 SCH-M(스키마 수정) 잠금을 획득합니다. 이 경우 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 따라서 기본 테이블을 업데이트할 수 없지만 SELECT 문과 같은 읽기 작업은 허용됩니다.
만약 운영중인 DB이고 언제든 ALTER 문 등이 일어나서 LOCK을 걸 수 있는 상황이라면 반드시 WITH(ONLINE=ON) 옵션을 걸어주도록 하자.
'Programming > MSSQL' 카테고리의 다른 글
| MSSQL 테이블 권한 조회(확인)하고 GRANT 문 만들기 (0) | 2018.06.09 |
|---|---|
| MSSQL 랜덤 패스워드 생성하기(난수형태 비밀번호 만들기) (0) | 2018.05.18 |
| MSSQL 인덱스 생성 CREATE INDEX (CLUSTRED, NONCLUSTRED, INCLUDED, FILTERED INDEX) (122) | 2018.04.25 |
| MSSQL 날짜 변환 (DATE CONVERT) (122) | 2018.04.24 |
| MSSQL 데이터 타입 크기 (DATA TYPE SIZE) (120) | 2018.04.24 |