데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

인덱스에 대한 SORT_IN_TEMPDB 옵션

 

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

 

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

 

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

 

 

인덱스를 생성할 때 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의 특성을 잘 파악하여 필요로 하는 곳에만 적용 할 수 있도록 하자.

 

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
» 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login