[MSSQL] 병렬처리(parallelism) 설정 & 분석

이번 시간에는 MSSQL 쿼리문을 실행하면 실행계획에서 볼 수 있는

병렬 처리 수준((MAXDOP : MAX DEGREE OF PARALLELISM)의 개념과 설정하는 방법에 대해서 알아보겠습니다.

 

개념 설명

병렬 처리 수준((MAXDOP : MAX DEGREE OF PARALLELISM)이란 쿼리가 한 개의 CPU로 일하는 것이 아니라 여러 CPU(코어)로 일을 분산해서 처리는 작업을 이야기합니다.

 

 

대부분의 OLTP 데이터베이스 쿼리문에서는 병렬 처리가 필요하지 않습니다. 불필요한 병렬 처리는 CPU 사용량을 낭비하는 주요 원인이지요. 병렬 처리가 발생하게 되면 쿼리가 병렬 처리가 발생하지 않게 튜닝을 하기를 권고합니다.

(물론 몇몇 쿼리는 병렬 처리가 최선의 방법일 수도 있습니다.)

 

그러니 성능 테스트를 통해서 서버 수준의 적절한 병렬 처리 수준을 지정하는 것이 가장 좋습니다.

전체 데이터베이스의 병렬 처리 설정을 할 수도 있으며 개별적인 쿼리에도 쿼리 마지막에 OPTION(MAXDOP N) 붙여서 사용하게 되면 병렬처리 수준을 개별적으로 설정 할 수 있습니다.

 

발생 원인

병렬처리 원인은 인덱스가 적절하지 않아서 SQL 서버 입장에서는 믿을 수 있는 것이 CPU 수량밖에 없어서 병렬 처리가 시작되게 됩니다. 다시 말해서 테이블에 인덱스가 없거나 또는 한 번에 많은 데이터를 가져와서 처리하기 때문입니다.

 

 

아래의 실행 계획을 보면  오렌지색 동그라미에 화살표 2개가 들어가 있는 작은 아이콘으로 병렬 처리가 되는 것을 알 수 있습니다. (SQL 상태 모니터링에서 보게 되면 Wait type이 CXPACKET (병렬 처리 대기)로 표시되어 있을 겁니다.)

 

병렬 처리 수준에 따른 CPU 및 쿼리 수행 시간

SQL Server에 병렬 처리 수준이 어떻게 설정되어 있는지 확인해 보겠습니다.

 

고급 옵션 표시 켜기(sp_configure)

sp_configure를 통해서 'max degree of parallelism' 값을 보기위해서는 고급 옵션을 활성화 시켜줘야 합니다.

아래의 쿼리를 실행한 다음 sp_configure를 실행해서 max degree of parallelism 값을 확인하세요

EXEC sp_configure 'show advanced options',1

RECONFIGURE --DB 재시작하지 않고 적용하기

GO

 

병렬 처리 완전 허용(기본값)

개별 쿼리에 대한 병렬처리 옵션 지정 검토(MAXDOP 1 또는 N)

EXEC sp_configure 'max degree of parallelism',0

RECONFIGURE --DB 재시작하지 않고 적용하기

GO

 

병렬 처리 완전 차단 --> 느려지는 쿼리 발생 --> 쿼리튜닝!!!!!!!!!

개별 쿼리에 대한 병렬 처리 옵션 지정 검토(MAXDOP 0 또는 N)

EXEC sp_configure 'max degree of parallelism',1

RECONFIGURE --DB 재시작하지 않고 적용하기

GO

 

병렬 처리 일부 허용 (쿼리 뒤에 입력  OPTION(MAXDOP N) )

개별 쿼리에 대한 병렬처리 옵션 지정 검토(MAXDOP 1 또는 0 또는 N)

EXEC sp_configure 'max degree of parallelism', N -- 0 : 병렬 처리 무한, 1:병렬 처리 차단, N:입력한 코어 숫자 만큼 써라

RECONFIGURE --DB 재시작하지 않고 적용하기

GO

 

 

SQL 프로파일러를 실행해서 해당 쿼리 창의 ID로 필터를 한 다음

병렬 처리 수준((MAXDOP : MAX DEGREE OF PARALLELISM)이 다른 3개의 쿼리를 개별 실행결과를 비교한 화면입니다.

병렬 처리를 완전 허용 했을 경우(MAXDOP 0)  CPU 사용률이 올라가고 실행시간(Duration)이 줄어들었고

병렬 처리를 완전 차단 했을 경우(MAXDOP 1) CPU 사용률이 내려가고 실행시간(Duration)이 증가한 것을 알 수 있다.

 

해결방법

병렬 처리 해결 방법은 인덱스가 없는 테이블에 적절한 인덱스를 만들어 주고, 한 번에 처리하는 데이터가 많을 경우 이를 여러 번 나누어서 처리하게 해서 병렬 처리가 발생하는 것을 없애주는 가장 좋습니다.