MAXDOP 극대화 하기

jevida(강성욱) 2016.10.08 06:53 Views : 2323

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에 대한 권장 사항 및 사례는 다음 링크를 참고 한다.

 

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

 

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

 

 

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

 

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

 

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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 36183
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 18705
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2091
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1902
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3553
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2579
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1653
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1844
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4821
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 2011
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1649
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1575
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1621
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7993
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 3182
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1293
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1846
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1898
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1443
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1532
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1932
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1764





XE Login