SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준

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

• 병렬 처리 수준

 

• Version : SQL Server 2005, 2008, 2008R2, 2012 (Enterprise, Developer 버전 지원)

 

SQL Server에서는 병렬처리 실행 또는 인덱스 DDL(데이터 정의 언어)작업의 각각의 인스턴스에 대해 가장 적합한 병렬 처리 수준이 자동으로 검색 된다. 이때 아래와 같은 조건을 기준으로 수행 된다.

• SMP(대칭적 다중 처리)컴퓨터와 같이 둘 이상의 마이크로프로세서 또는 CPU가 있는 컴퓨터에 SQL Server가 실행 중이어야 한다.

 

SMP 관련 링크 : http://sqlmvp.kr/140188414498

 

• 사용할 수 있는 스레드 수가 충분 해야 한다. 쿼리 또는 인덱스 작업을 실행하려면 일정 수의 스레드가 필요하다. 특정 병렬 처리 수준에 대한 병렬 계획의 스레드 요구 사항이 충족되지 않을 경우에는 데이터베이스 엔진에서 병렬 처리 수준을 자동으로 낮추거나 지정된 작업 컨텍스트의 병렬 계획을 완전히 중단 한다. 그런 다음 하나의 스레드만을 사용되는 직렬 계획을 실행 한다.

 

• 실행한 쿼리 또는 인덱스 작업 유형 : 병렬 실행 계획은 인덱스 생성, 리빌드, 클러스터형 인덱스 및 CPU 사용량이 큰 쿼리를 삭제하는 등의 인덱스 작업에 적합하다. 예를 들어 대용량 테이블의 조인, 대규모 집계 및 정렬이 병렬 쿼리에 적합하다.

 

• 처리할 행 수가 충분한지 여부 : 쿼리 최적화 프로그램에서 행 수가 부족하다고 판단하는 경우 행을 배포하기 위해 교환 연산자를 사용하지 않는다. 결과적으로 연산자는 직렬로 실행 된다. 시작, 배포, 조정 비용이 병렬 연산자 실행으로 얻은 이익보다 큰 경우 연산자를 직렬 계획으로 실행 하면 이 시나리오를 피할 수 있다.
• 최신 배포 통계를 사용할 수 있는지 여부 : 가장 높은 병렬 처리 수준을 제공할 수 없는 경우 병렬 처리를 중단하기 전에 더 낮은 병렬 처리 수준이 가능한지 확인 한다. 예를 들어 뷰에서 클러스터형 인덱스를 만드는 경우 클러스터형 인덱스가 아직 생성되어 있지 않았으므로 배포 통계를 계산할 수 없다. 이 경우 데이터베이스 엔진은 이 인덱스 작업에 가장 높은 병렬 처리 수준을 할당하지 않는다. 그러나 이 경우 정렬 또는 검색과 같은 일부 연산자에는 병렬 처리의 이점이 적용 될 수 있다.

 

데이터베이스 엔진은 실행 시 현재 시스템 작업과 구성 정보에서 병렬 실행이 가능한지 확이 한다. 병렬 실행이 보장되는 경우 데이터베이스 엔진은 최적의 스레드 수를 결정하고 이 스레드에 병렬 계획을 분산하여 실행 한다. 병렬 실행을 위한 쿼리 또는 인덱스 작업이 여러 스레드에서 실행되기 시작하면 해당 작업이 완료 될 때까지 동일한 수의 스레드가 사용 된다.

 

 

데이터베이스 엔진은 프로시저 캐시에서 실행 계획을 가져올 때마다 최적의 스레드 수를 다시 검사한다. 예를 들어 쿼리를 한번 실행할 때는 직렬 계획을 사용할 수 있고 동일한 쿼리를 두 번째 실행 할 경우 세 개의 스레드를 사용하는 병렬 계획을 사용할 수 있다.

 

병렬 쿼리 실행 계획에서 삽입, 업데이트, 삭제 작업은 직렬로 실행 된다. 그러나 UPDATE, DELETE 문의 WHERE 절이나 INSERT 문의 SELECT 부분은 병렬로 실행 될 수 있다. 그런 다음 실제 데이터 변경은 직렬로 실행 된다.

 

정적 커서 및 키 집합 커서는 병렬 실행 계획에 따라 채워 질 수 있다. 그러나 동적 커서의 동작은 직렬 실행에 의해서만 제공 될 수 있다. 쿼리 최적화 프로그램은 동적 커서에 포함된 쿼리에 대해서는 항상 직렬 실행 계획을 생성한다.

 

OLTP성의 단순한 쿼리 경우 병렬처리로 실행 될 경우 필요한 추가 조정 작업으로 인하여 성능이 저하 될 수도 있다. 데이터베이스 엔진은 병렬처리에 적합한 쿼리와 그렇지 않은 쿼리를 구분하기 위해 쿼리 또는 인덱스 작업 실행 시 예상되는 비용을 cost threshold for parallelism 값과 비교한다.

 

Cost threshold for parallelism 옵션은 SQL Server가 쿼리에 대한 병렬 계획을 만들고 실행할 임계값을 지정 할 수 있다. SQL Server는 동일한 쿼리에 대해 직렬 계획을 실행하는데 드는 예상비용이 cost threshold for parallelism에 설정된 값보다 높은 경우에만 해당 쿼리에 대한 병렬 계획을 만들고 실행 한다. 경우에 따라 쿼리 비용이 현재 cost threshold for parallelism 값보다 작아도 병렬 계획을 선택할 수 있다.

 

Cost threshold for parallelism 옵션은 0 ~ 32767 사이의 값으로 설정 할 수 있으며 기본 값은 5이다.

 

 

[Cost threshold for parallelism 임계값 설정]

아래 스크립트는 병렬처리에 대한 임계값을 10초로 설정한 예이다.

sp_configure 'show advanced options', 1; GO   reconfigure; GO   sp_configure 'cost threshold for parallelism', 10; GO   reconfigure; GO

 

 

 

다음의 조건에서는 Cost threshold for parallelism 값을 무시한다.

• SQL Server가 실행되는 컴퓨터의 프로세서가 하나만 있을 경우
• Affinity mask 구성 옵션 때문에 SQL Server가 단일 CPU만 사용하는 경우
• Max degree of parallelism 옵션이 1로 설정된 경우

 

 

사용자는 MAXDOP(Max Degree Of Parallelism)옵션을 사용하여 프로세스 수를 제한 할 수 있다. MAXDOP 옵션을 사용하면 개별 쿼리 작업을 보다 상세히 제어 할 수 있다. MAXDOP 옵션을 0으로 설정하면 SQL Server에서는 병렬 계획 실행에서 최대 64개의 프로세서까지 사용 할 수 있다.

 

 

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

 

 

[Parallel Execution as Multiple Serial Plans]

 

 

[Parallel Page Supplier]

 

 

[Execution Contexts]

 

 

[exchange operator has two distinct sub-components]

 

 

[Logical Operations]

 

 

[참고 자료]

병렬 처리 수준 : http://msdn.microsoft.com/ko-kr/library/ms188611(v=sql.105).aspx

 

Understanding and Using Parallelism in SQL Server :

https://www.simple-talk.com/content/article.aspx?article=1250

 

cost threshold for parallelism 옵션 :

http://msdn.microsoft.com/ko-kr/library/ms188603(v=sql.105).aspx

 

SMP 관련 링크 : http://sqlmvp.kr/140188414498

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

 

 

 

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