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

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

MAXDOP 극대화 하기

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

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
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1449
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2270
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1706
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2852
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4022
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1347
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1286
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1461
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2646
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2339
» MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 1998
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2018
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1840
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3433
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2260





XE Login