SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업

SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리

• 병렬 인덱스 작업

 

• Version : SQL Server 2005, 2008, 2008R2, 2012 (Enterprise Edition only)

 

인덱스를 생성하거나 재생성 하는 작업 또는 클러스터형 인덱스를 삭제하는 작업을 위해 작성된 쿼리 계획에서는 여러 마이크로프로세서가 있는 컴퓨터에서 병렬 다중 스레드 작업을 할 수 있다.

 

SQL Server는 다른 쿼리에 사용하는 것과 동일한 알고리즘을 사용하여 인덱스 작업에 대한 병렬처리 수준(실행할 스레드 수)을 결정한다. 인덱스 작업에 대한 최대 병렬 처리 수준은 max degree of parallelism 서버 구성 옵션을 따른다.

 

• SQL Server MAX DOP 설정 : http://sqlmvp.kr/140176043445

 

CREATE INDEX, ALTER INDEX, DROP INDEX 및 ALTER TABLE 문에서 MAXDOP 인덱스 옵션을 설정하여 개별 인덱스 작업에 대한 max degree of parallelism 값을 재정의 할 수 있다.

 

데이터베이스 엔진에서 인덱스 실행 계획을 작성하는 경우 병렬 작업의 수는 가장 낮은 값으로 설정 된다.

• 컴퓨터의 마이크로 프로세서의 수(또는 CPU)
• max degree of parallelism 서버 구성
• SQL Server 스레드에 대해 수행된 작업의 임계값을 넘지 않은 CPU 수
• SQL Server 데이터베이스 엔진이 시스템에서 진행 중인 작업이 많음을 감지하면 실행 되기 전 인덱스 작업의 병렬처리 수준이 자동으로 감소 한다.
• 분할되지 않은 인덱스의 선행 키 열의 고유 값 수가 제한되거나 각 고유 값의 빈도가 상당히 다양한 경우 데이터 베이스 엔진은 병렬 처리 수준을 줄일 수도 있다.

 

예를 들어 8개의 CPU가 있으나 max degree of parallelism이 6으로 설정된 경우 최대 병렬 스레드 수는 6이다. 인덱스 실행 계획을 작성 할 때 컴퓨터에 있는 5개의 CPU가 SQL Server 작업의 임계값을 초과하는 경우 실행 계획은 3개의 병렬 스레드만 지정 한다.

 

병렬 인덱스 작업의 주요 단계는 다음과 같다.

• 조정 스레드(coordination thread)는 신속하게 무작위로 테이블을 검색하여 인덱스 키의 분포를 예상한다.
• 조정 스레드(coordination thread)는 병렬 작업 수준에 해당하는 여러 키 범위를 만드는 키 경계를 설정한다. 여기서 각 키 범위는 비슷한 개수의 행을 포함할 수 있도록 결정 된다.(모든 CPU를 사용하도록 충분한 키 범위를 설정 할 수 없는 경우 병렬 처리 수준도 이에 따라 줄어 든다.)

 

• 조정 스레드(coordination thread)는 병렬 작업 수준에 해당하는 여러 스레드를 디스패치하고 이러한 스레드가 작업을 완료할 때까지 대기 한다.
• 각 스레드는 해당 스레드에 할당된 범위 내의 키 값을 갖는 행만 검색하는 필터를 사용하여 기본 테이블을 검색 한다.
• 각 스레드는 키 범위에서 행의 인덱스 구조를 작성 한다. 분할된 인덱스의 경우 각 스레드는 지정한 수만큼의 파티션을 작성한다. 파티션은 스레드간에 공유되지 않는다.
• 모든 병렬 스레드가 완료 된 후 조정 스레드는 인덱스 하위 단위를 단일 인덱스에 연결 한다. (오프라인에서만 가능)

 

 

 

[온라인 인덱스 작업]

온라인 인덱스 작업은 인덱스 작업 중 동시 사용자 작업을 허용한다. MAXDOP 옵션을 사용하여 온라인 인덱스 작업에만 사용되는 최대 프로세서 수를 제어할 수 있다. 이렇게 하면 인덱스 작업에 사용되는 리소스와 현재 사용자가 사용하는 리소스를 적절히 조정할 수 있다.

 

 

[분할 인덱스 작업]

쿼리 최적화 프로그램에서 작업 작업에 병렬 처리 수준을 적용할 경우 정렬이 필요한 분할 인덱스 작업의 메모리 요구 사항이 늘어날 수 있다. 병렬 처리 수준이 높을수록 메모리 요구사항이 늘어난다.

 

 

[참고 자료]

병렬 인덱스 작업 : http://msdn.microsoft.com/ko-kr/library/ms191292(v=sql.105).aspx

병렬 인덱스 작업 구성 : http://msdn.microsoft.com/ko-kr/library/ms189329(v=sql.105).aspx

온라인 인덱스 구조 : http://msdn.microsoft.com/ko-kr/library/ms191261(v=sql.105).aspx

분할된 테이블 및 인덱스 : http://technet.microsoft.com/ko-kr/library/ms345146(v=sql.90).aspx

 

 

 

2013-05-21 / 강성욱 / http://sqlmvp.kr