인덱스에 대한 SORT_IN_TEMPDB 옵션

인덱스에 대한 SORT_IN_TEMPDB 옵션

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

인덱스를 만들거나 리빌드 할 때 발생하는 디스크 공간의 추가 요구 사항이 발생하는 케이스를 살펴 보았다.

 

[인덱스 작업의 디스크 공간 요구 사항]

• http://sqlmvp.kr/140180087406

 

 

인덱스를 생성할 때 SORTIN_TEMPDB를 사용하여 성능 향상을 시킬 수 있다고 하였는데 SORT_IN_TEMPDB에 대해서 자세히 알아 보자.

 

SORT_IN_TEMPDB 옵션은 인덱스를 만들거나 리빌드 할 때 SQL Server 데이터베이스 엔진에서 인덱스를 만드는데 사용되는 중간 정렬 결과를 tempdb에 저장한다. tempdb가 데이터가 위치한 드라이브가 아닌 다른 빠른 디스크에 위치한다면 인덱스 생성시간을 줄일 수 있다.

 

[SORT_IN_TEMPDB = OFF]

기본적으로 정렬 작업 시 대상 파일 그룹에 저장된다. 이 때 기본 테이블 페이지의 읽기와 정렬 작업의 쓰기가 번갈아 수행되어 디스크의 한 영역에서 다른 영역으로 디스크 읽기/쓰기 헤드가 이동된다. 이 헤드는 데이터 페이지가 검색 될 때 데이터 페이지 영역에 있다가 정렬 작업이 디크스에 기록 될 때 헤드는 빈 영역으로 이동했다가 다시 테이블 페이지가 검색이 시작 되면 다시 데이터 페이지 영역으로 돌아간다.

 

 

[SORT_IN_TEMPDB = ON]

중간 정렬의 결과 작업을 tempdb에 저장한다. 이 때 중요한 것은 Tempdb가 대상 파일그룹과 다른 개별 디스크에 위치 하여야 한다는 것이다. 쓰기를 진행 할 때 디스크가 데이터 키를 읽을 때 좀더 연속적으로 진행되며 tempdb 디스크에 대한 쓰기 역시 연속적으로 진행 된다. 다른 사용자가 해당 데이터베이스를 사용하고 별도의 디스크 주고를 액세스 하는 경우에도 읽기 및 쓰기의 성능이 향상된다.

 

[특징]

• SORT_IN_TEMPDB 옵션은 CREATE INDEX 작업이 병렬로 처리되지 않는 경우 인덱스 익스텐트의 근접성을 향상 시킬 수 있다.
• SROT_IN_TEMPDB 옵션은 현재 쿼리문에만 영향을 준다.
• 정렬작업이 필요하지 않거나 메모리에서 정렬을 수행 할 수 있으면 SORT_IN_TEMPDB 옵션은 무시 된다.

 

 

[INDEX CREATE MEMORY 구성]

가용할 수 있는 메모리에 따라 SORT_IN_TEMPDB 상황이 발생 한다. 인덱스 생성 메모리 옵션은 인덱스를 만들기 위해 처음으로 할당되는 최대 메모리 양을 제어 한다. 인덱스 생성시 가용할 수 있는 메모리 구성 방법을 알아 보자.

 

현재 메모리 설정을 확인 및 사용자 지정을 할 수 있다. 기본값은 0이며 필요에 따라 동적으로 할당 한다. 실습 스크립트는 고정으로 204800KB로 설정 한다.

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1 GO RECONFIGURE WITH OVERRIDE GO   EXEC SP_CONFIGURE 'INDEX CREATE MEMORY', 204800 GO   RECONFIGURE WITH OVERRIDE GO   EXEC SP_CONFIGURE

 

 

SSMS에서 UI를 이용하여 할당 할 수도 있다. 서버 속성에서 [메모리] – [기타 메모리 옵션]에서 설정 할 수 있다.

 

• 쿼리당 최소 메모리 옵션의 설정이 인덱스 생성 메모리 옵션보다 우선 한다.
• 인덱스 생성 메모리가 쿼리당 최소 메모리보다 적은 경우 경고 메시지가 나타나지만 값은 설정 된다.
• 분할된 테이블 및 인덱스를 사용할 때 분할된 인덱스가 정렬되지 않고 병렬 처리 수준이 높은 경우 인덱스를 만드는데 필요한 최소 메모리 요구 사항이 높아 질 수 있다.
• 설정도니 양이 쿼리 실행에 필요한 최소 양보다 적은 경우 오류 메시지가 나타난다.
• 이 옵션은 하드웨어 플랫폼에서 사용할 수 있는 실제 메모리 양을 초과 할 수 없다.
• 32비트 운영체제에서 실행 값은 3GB 미만이다.

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/ms180967.aspx

http://msdn.microsoft.com/ko-kr/library/ms188281.aspx

 

 

무분별한 옵션 설정은 시스템에 문제를 발생 시킬 수 있다. 자신이 운영하는 DB의 특성을 잘 파악하여 필요로 하는 곳에만 적용 할 수 있도록 하자.

 

 

2013-02-08 / 강성욱 / http://sqlmvp.kr / http://datawaffle.com