1 기본적인 사항

2 병렬처리 임계값의 조정

3 병렬처리의 원인

4 병렬처리 강제화

5 2016 SP1 CU3+

6 참고자료

CPU가 2개 이상인 시스템에서는 쿼리비용의 임계값(기본: 5)가 넘어가면 병렬쿼리를 한다. 즉, CPU 여러 장이 나누어서 읽고, 스트림을 수집하는 과정이다. 병렬쿼리는 CPU가 여러 개 있어야 하며, 충분한 메모리를 가지고 있어야 한다. 다음은 도움말의 병렬쿼리 예제이다.

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

MSSQL Server는 병렬쿼리를 수행은 작업은 큰 작업이라고 볼 수 있다. 그러므로 대부분은 Merge Join 또는 Hash Join으로 쿼리플랜을 세운다. 위의 병렬쿼리는 각각의 CPU가 읽을 부분을 나누어 읽은 뒤 Merge Join을 하기 위해서 스트림 파티션을 변경하고, 정렬 후 Sort Merge Join을 한다. 또한 스트림을 집계, 수집하는 과정도 포함된다. 이러한 작업은 큰 트랜잭션의 처리를 위한 것이다. 그러나 일반적인 OLTP 환경에서 병렬 쿼리플랜이 자주 목격된다면 SQL 튜닝이 필요할 것이고, SQL 튜닝에도 계속적인 병렬 쿼리플랜이 목격된다면 설계문제로 인한 것이다. 대부분의 OLTP성 업무들은 트랜잭션의 크기가 아주 작다. 그러므로 직렬쿼리가 대부분 유리하다. 아무리 SQL 튜닝을 해도 병렬 쿼리플랜이 고쳐지지 않는다면 다음과 같이 힌트를 사용해야 한다.

SELECT …
FROM …
OPTION (MAXDOP 1)

다은 방법으로는 다음 그림과 같이 쿼리 병렬 실행의 경우 최소 쿼리 계획 임계값을 조정하는 방법이다. 아래대로 설정하면 쿼리의 예상비용이 100 이 넘어가지 않으면 병렬쿼리는 수행되지 않는다. 또한 병렬쿼리가 자주 수행된다면 병렬쿼리에 사용될 프로세서 수를 제한하는 것도 병렬 쿼리 중에 다른 작업에 영향을 미치지 않도록 하는 하나의 방법이다.

위의 임계값은 다음과 같이 조정할 수 있다.

EXEC master..sp_configure 'show advanced option', '1'
EXEC master..sp_configure 'cost threshold for parallelism', '100'
RECONFIGURE WITH OVERRIDE
EXEC master..sp_configure 'show advanced option', '0'

경험적으로 다음과 같은 경우가 병렬처리 원인이 된다.

1. 쿼리 잘못짰다
   
2. 예상실행계획 비용이 설정값(기본 5)보다 높다. (옵티마이저의 잘못된 예상비용산정)
   
3. 테이블의 디자인이 조낸 꾸지다.
   
4. 쿼리의 실행 비용이 원래 높다.
   
   

2008버전의 경우는 Parallelism Enhancements in SQL Server 2008 문서를 참고하기 바란다.

이 방법은 문서화되지 않은 dbcc 명령을 통해 구현할 수 있다. cpu와 io에 대한 가중치를 옵티마이저에게 알려줄 수 있다. 최종적인 쿼리 비용이 설정된 값(기본값 = 5)보다 크면 병렬처리하므로 cpu에 대한 가중치로 병렬처리를 유도할 수 있다. 먼저 설정값을 본다.

DBCC TRACEON (3604); -- Show DBCC output
DBCC SHOWWEIGHTS; -- Show the settings

/* 결과
DBCC Opt Weights CPU: 1.000000  IO: 1.000000 SPID 81
*/

병렬처리 하지 않는 쿼리의 실행 계획을 보자.

select count(*) from master.dbo.spt_values

위 그림을 보면 알 수 있겠지만 병렬처리 하지 않는다. cpu에 가중치를 주어 쿼리 비용을 5가 넘어가도록 조정해 보자.

DBCC FREEPROCCACHE
DBCC SETCPUWEIGHT(10000); 
go

select count(*) from master.dbo.spt_values
option (recompile, querytraceon 8649) -- 옵션은 꼭 필요하다.
go

DBCC SETCPUWEIGHT(1); -- 원래대로 해놓자.
DBCC SETIOWEIGHT(1); --io에도 가중치를 줄 수 있다.
go

필자의 경우는 cpu에 10000 정도를 주었더니 쿼리 비용이 5를 넘어가 병렬처리를 하는 것을 볼 수 있었다. 그렇다면 비용이 5가 넘어가게 io도 가중치를 주면 병렬처리를 하지 않을까하는 생각을 해봤다. sql server 2008 r2에서 DBCC SETIOWEIGHT(10000); 과 같이 io에 가중치를 주어봤으나 병렬처리 실행 계획이 만들어지지 않았다. 이때 cpu 가중치는 1이었다. 하지만 DBCC SETCPUWEIGHT(10); 과 같이 가중치를 주니 병렬처리 실행계획을 세우더라. io와 cpu 가중치를 적절히 주어 실행계획을 유도시키면 강제로 병렬처리 할 수 있음을 알 수 있다.

DBCC FREEPROCCACHE
DBCC SETCPUWEIGHT(10); 
DBCC SETIOWEIGHT(10000); 
go

select count(*) from master.dbo.spt_values
option (recompile, querytraceon 8649)
go

DBCC SETCPUWEIGHT(1); -- Default CPU weight
DBCC SETIOWEIGHT(1);
go

뭐...TRACEON 했던거 TRACEOFF 하자.

DBCC TRACEOFF (3604);

DBCC 명령을 계속 사용할 수 없으니, play_guide(계획지침)를 이용하면 되것다. 또는 다음과 같이 병렬 실행 계획을 xml로 떠서 실행하면 되것다.

select count(*) from master.dbo.spt_values
option (use plan N'<?xml version="1.0" encoding="utf-16"?>
...
...
...
--소스가 길어서 아래의 source_01.txt로 첨부하였다.

source_01.txt (새창에서 열림)

특이한 점은 예상하위트리 비용이 5를 넘지 않았다는 것이다. 어찌되었건 sql server는 힌트로 준 실행계획을 그대로 사용한 것이다. 뭐.. 가끔씩 쓸만할라나? ㅇㅇ DW에서는 조낸 쓸만하다.

OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

• SQL Server 2012’s Information on Parallel Thread Usage
  
• Forcing a Parallel Query Execution Plan
  
• A closer look at CXPACKET wait type in SQL Server
  
• Managing Max Degree of Parallelism