MAXDOP 극대화 하기

MAXDOP 극대화 하기

 

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

 

SQL Server에서는 CPU가 2개 이상의 코어를 가지고 있을 때 상황에 따라 자동적으로 병렬처리를 한다. 그렇다면 CPU의 코어가 많을수록 더 좋은 성능을 내는가? 최대 병렬처리 성능을 끌어내기 위해서는 어떻게 해야 할까?

 

이번 아티클은 CSS SQL Server Engineers 블로그에 게시된 내용으로 CPU 80코어의 시스템에서 MAXDOP의 성능을 극대화 하는 방법을 소개 하였다. 이번에 게시된 글은 특정 시스템 환경의 사례이므로 모든 시스템에 적용할 수 있는 일반적인 구성과 다를 수 있다. 필자가 읽고 이해한 내용을 바탕으로 정리 하였으며 번역의 오류나 기술적 오류 가능성을 미리 알려 둔다.

 

 

CPU 80코어 환경에서 인덱스 리빌드시 64코어만 사용하는 이슈가 있었다. 나는 조심스럽게 sys.dm_os_spinlock_stats 와 sys.dm_os_wait_stats, 성능 카운터, 메모리 사용 패턴, I/O 활동에 대해서 공부하였다.

 

나는 SQL Server CPU 바운드에 더 많은 CPU를 추가하면 인덱스 리빌드 성능에 이득이 있을것이라고 확신 하였다. 사실 80 CPU, 2TB RAM, 4TB SSD시스템을 가지고 있었다.

 

참고적으로 메모리 또는 I/O 병목현상이 있는 상황에서 CPU를 추가하면 이로 인한 성능저하로 더 큰 문제가 발생 할 수 있다.

 

MAXDOP 튜닝에서 많은 참조는 병렬처리의 오버헤드는 줄이고 쿼리를 최선으로 실행할 수 있도록 한다. MAXDOP에 대한 권장 사항 및 사례는 다음 링크를 참고 한다.

• http://support.microsoft.com/kb/2806535
• http://blogs.msdn.com/b/psssql/archive/2013/08/28/i-want-to-know-why-my-query-is-not-parallelized.aspx
• http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx
• http://blogs.msdn.com/b/psssql/archive/2011/02/03/resource-governor-and-maxdop-parallel-plan-generation.aspx
• http://blogs.msdn.com/b/psssql/archive/2008/02/13/how-it-works-sql-server-per-query-degree-of-parallelism-worker-count-s.aspx

 

이 게시물은 현재의 권고를 모순하기 위한 것은 아니다. 이 블로그는 전적으로 특정 관리 대상에 초점을 맞추고 있다. 다양한 성능과 높은 병렬 처리 수준으로 인덱스 리빌드를 빠르게 완료하기 위한 방법을 리뷰 한다.

 

참고적으로 상황에 따라 온라인 인덱스 리빌드 시 더 많은 조각화가 발생 할 수도 있다.

• (원문) : http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx
• (번역) http://sqlmvp.kr/140193622452

 

 

쿼리를 처리를 하는데 있어서 병렬 쿼리 처리를 결정하기 위해서는 몇 가지 단계가 있다.

 

1 단계 – 컴파일 : 힌트, sp_configure과 리소스 그룹에서 병렬 계획을 고려하여 쿼리 작업이 병렬 실행을 허용할 경우

If hint is present and > 1 then build a parallel plan   else if no hint or hint (MAXDOP = 0)   if sp_configure setting is 1 but workload group > 1 then build a parallel plan   else if sp_configure setting is 0 or > 1 then build parallel plan

 

 

2 단계 – 쿼리 실행 : 쿼리를 실행하면 런타임으로 병렬 처리 수준이 결정된다. 이 수준은 많은 요소가 포함 된다. 다음 링크에서 자세히 확인 할 수 있다.

• http://technet.microsoft.com/en-US/library/ms178065(v=SQL.105).aspx

 

if sp_configure or query hint forcing serial plan use (1)   else if resource workgroup set   if query hint present use min(hint, resource workgroup)   else use resource workgroup

 

 

만약 64로 설정되어 있는데도 불구하고 계산 후 0인 경우가 있다. 이는 80 CPU시스템에 2개의 윈도우 스케줄러 그룹을 40개 가지고 있기 때문이다. 나는 윈도우 스케줄러의 교차 방지를 위해 40 CPU 캡을 예상 할 수 있다. 이 경우 MAXDOP의 런타임 목표는 여전히 0(디폴트)이다.

 

SQL Server는 Sys.dm_os_schedulers를 사용하여 런웨이 태스크를 식별할 수 있다. 이전 버전에서는 SQL Server는 폴링 메커니즘(1초 간격)으로 작업 대상을 선정한다. 이는 많은 CPU 사용량이 발생 하였을 때 여러 쿼리에서 병렬처리 사용 시 동일한 노드에 대해 병목이 발생 한다. 새로운 빌드에서는 병렬 쿼리로 인한 동일한 집합의 할당 가능성을 줄일 수 있다.

 

select * from Sys.dm_os_schedulers

 

 

 

추적 플래그 24666을 통하여 이전 버전에서 사용가능한 리소스의 가용량을 제한 할 수 있다. 작업자의 위치 정보는 CPU가 병렬처리 작업을 할당 할 때 사용된다. 일반적으로 배치 결정은 다음과 같다.

• SMP (FPlaceThreadsOneNodeSystem): SMP와 단일 노드 시스템과 같은 단일 노스 시스템에 동일한 것을 요구하는 경우. SOFT NUMA와 affinity는 SQL에서 SMP로 처리될 수 있다.
• CONNECTION (FPlaceThreadsOneNodeSystem): 추적 플래그 2479를 사용하는 경우 모든 병렬 결정이 연결과 연관된 노드로 제한한다. SOFT NUMA 또는 커넥션 노드, node affinity는 도움이 될 수 있다.
• FULL (FPlaceThreadsAllNodes): MAXDOP 대상의 모든 스케줄러에 대한 모든 스케줄러 대기열 작업이 같은 경우
• LEAST (FPlaceThreadsWithinLeastLoadedNode): 추적플래그 2467을 사용하는 경우 MAXDOP 대상이 단일 노드보다 작은 부하를 찾을 수 있다.
• SPREAD (FPlaceThreadsMultipleNodes): 사용 가능 한 모든 노드에 부하 분산.

 

 

XEvent를 사용하면 MAXDOP 의사 결정 로직을 모니터링 할 수 있다.

• XeSqlBok::calculate_dop_begin
• XeSqlPkg::calculate_dop

 

처음에 언급하였듯이 인덱스 리빌드 작업 시 80개의 CPU 모두를 사용하기 위하여 몇 가지 설정을 한다.

• MAXDOP 쿼리힌트에 80을 명시
• 모든 쿼리에 적용할 수 있도록 SP_Configure에서 최대 병렬처리 수준을 80으로 명시
• 리소스 풀/ 작업 그룹 설정에서 MAXDOP = 80을 생성하고 인덱스가 리소스 관리자 분류를 사용하여 이 연결 구축에만 할당한다.

 

다음 표는 다른 설정 수준에서 80 CPU에 대한 MAXDOP 결과이다. (일부 구성은 프로시저 캐시(DBCC freeprocecache) 또는 disconnect/connect 페어링을 flush 할 필요가 있다.)

 

우리는 sys.dm_os_tasks를 사용하여 병렬 처리를 모니터링 할 수 있다.

select * from sys.dm_os_tasks

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx

 

 

2013-09-30 / 강성욱 / http://sqlmvp.kr