SQLServer 옵티마이져는 최적화된 실행계획을 이끌어 내려 많은 노력을 하는데요.
이 노력의 단계중 예상실행 비용(Estimated Subtree Cost)과 cost threshold for parallelism에 설정된 비용(기본 5)과
비교해 직렬계획을 세울지 병렬계획을 세울지 선택하는 단계가 포함돼있습니다.(물론 멀티 프로세서 환경에서..)
cost threshold for parallelism 설정값은 직렬로 처리하기보다 여러 스레드(CPU)를 이용해 병렬로 분산처리하는게
더 효율적이라고 판단되는 기준값입니다.
병렬처리에 사용될 스레드수는 실행계획단계가 아닌 실행바로 전에 결정이 되고, 동일한 실행계획을 실행할때마다
스레수는 다를수 있습니다. 병렬계획 임계값 기본값은 5이며 예상비용이 임계값보다 작은경우
실행문의 OPTION (MAXDOP n) 힌트 지시는 의미가 없게 됩니다.
OLTP환경에서의 병렬처리는 되도록 최소화 해야합니다. 병렬처리는 많은 리소스(특히 CPU)를 사용해야 하므로, 다른 처리에
적지 않은 영향을 줄수있기 때문입니다.
OLAP환경에서의 병렬처리는 되도록 최대화 해야합니다. 대량작업시 여러 스레드가 분할해서 일을 하지 않으면
많은 시간이 소모돼는 작업들이 많기 때문입니다.(메모리나 디스크 병목도 함께 모니터링을 해야합니다.)
예제를 통해 병렬처리 흐름을 확인해 보겠습니다.
/* 테스트테이블생성및데이터입력*/
IF OBJECT_ID('orders') IS NOT NULL DROP TABLE orders
IF OBJECT_ID('lineitem') IS NOT NULL DROP TABLE lineitem
CREATE TABLE orders(
o_orderdate DATETIME
,o_orderkey INT
,o_custkey INT
,o_orderpriority INT
,o_column CHAR(1000))
GO
CREATE TABLE lineitem(
l_orderkey INT
,l_receiptdate DATETIME
,l_commitdate DATETIME
,l_shipdate DATETIME
,l_column CHAR(1000))
GO
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1
WHILE(1=1)
BEGIN
INSERT INTO orders VALUES(GETDATE(),@i,@i,@i,'A')
INSERT INTO lineitem VALUES
(@i,GETDATE(),GETDATE(),GETDATE(),'B')
,(@i,GETDATE()+(CASE WHEN (@i%2) = 1 THEN 1 ELSE 0 END),GETDATE(),GETDATE(),'B')
,(@i,GETDATE()+(CASE WHEN (@i%2) = 1 THEN 1 ELSE 0 END),GETDATE(),GETDATE(),'B')
,(@i,GETDATE()+(CASE WHEN (@i%2) = 1 THEN 1 ELSE 0 END),GETDATE(),GETDATE(),'B')
IF(@i >= 100000) BREAK;
SET @i += 1;
END
COMMIT TRANSACTION
GO
/* 인덱스생성통계업데이트*/
CREATE INDEX l_order_dates_idx ON lineitem (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)
CREATE UNIQUE INDEX o_datkeyopr_idx ON ORDERS (o_orderdate, o_orderkey, o_custkey, o_orderpriority)
GO
UPDATE STATISTICS orders
UPDATE STATISTICS lineitem;
GO
SET STATISTICS PROFILE ON
SET STATISTICS PROFILE OFF
GO
/* 대략여기서나온결과값o_orderdate 를쿼리조건에입력하겠습니다.*/
SELECT * FROM orders WHERE o_orderkey = 50000
결과값: 2010-11-06 21:14:18.960
/* 실행*/
SET ROWCOUNT 1
SELECT
o_orderpriority
,COUNT(*) AS Order_Count
FROM orders
WHERE
o_orderdate >= '2010-11-06 00:00:00.000'
AND o_orderdate < '2010-11-06 21:14:18.960'
AND EXISTS(
SELECT
*
FROM lineitem
WHERE
l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority
OPTION (HASH JOIN)
SET ROWCOUNT 0
GO
위 스크립트에 대한 실행계획은 대략 아래 와 같습니다.
|--Parallelism(Gather Streams, ORDER BY:([IndexTest].[dbo].[orders].[o_orderpriority] ASC))
분할됐던 스트림을 최종적으로 단일스트림으로 결합합니다.
|--Sort(ORDER BY:([IndexTest].[dbo].[orders].[o_orderpriority] ASC))
|--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1013],0)))
o_orderpriority 별카운트 집계후 정렬 합니다.(Compute Scalar 연산자를통한계산식수행후스칼라값생성)
|--Hash Match(Aggregate, HASH:([IndexTest].[dbo].[orders].[o_orderpriority]), RESIDUAL:([IndexTest].[dbo].[orders].[o_orderpriority] = [IndexTest].[dbo].[orders].[o_orderpriority]) DEFINE:([Expr1013]=COUNT(*)))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([IndexTest].[dbo].[orders].[o_orderpriority]))
|--Hash Match(Left Semi Join, HASH:([IndexTest].[dbo].[orders].[o_orderkey])=([IndexTest].[dbo].[lineitem].[l_orderkey]), RESIDUAL:([IndexTest].[dbo].[lineitem].[l_orderkey]=[IndexTest].[dbo].[orders].[o_orderkey]))
Index Seek 범위를 데이터를 빌드집합기준으로 해시조인을 합니다.
조인된 결과스트림을 다시 분할합니다.
후에Hash Match Aggregate 연산자로o_orderpriority 기준으로그룹핑합니다.
|--Bitmap(HASH:([IndexTest].[dbo].[orders].[o_orderkey]), DEFINE:([Bitmap1012]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([IndexTest].[dbo].[orders].[o_orderkey]))
|--Index Seek(OBJECT:([IndexTest].[dbo].[orders].[o_datkeyopr_idx]), SEEK:([IndexTest].[dbo].[orders].[o_orderdate] >= '2010-11-06 00:00:00.000' AND [IndexTest].[dbo].[orders].[o_orderdate] < '2010-11-06 21:14:18.960') ORDERED FORWARD)
o_orderdate 조건에Index Seek 범위데이터를Parallelism의Repartition Streams 연산자가데이터(스트림)을o_orderkey 조건자를 기준으로 여러 쓰레드(CPU)에분할 합니다.
Bitmap 연산자의 추가로 비트맵필터링이 이뤄집니다.
Bitmap 연산자는 현재트리의집합을 비트맵인덱스 매커니즘을 이용해 메모리내에 인덱스를 생성하게 되며,
비트맵필터가 해시조인의 빌드집합에서 생성되지만 실제필터링은 검색집합에서 이뤄 집니다.
위 해시조인에 대한 검색집합은 바로 아래의 실행계획에서 WHERE:(PROBE([Bitmap1012] 조건자를 이용해 rows가 필터된 결과를 보여주고 있습니다.
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([IndexTest].[dbo].[lineitem].[l_orderkey]), WHERE:(PROBE([Bitmap1012],[IndexTest].[dbo].[lineitem].[l_orderkey])))
|--Stream Aggregate(GROUP BY:([IndexTest].[dbo].[lineitem].[l_orderkey]))
|--Index Scan(OBJECT:([IndexTest].[dbo].[lineitem].[l_order_dates_idx]), WHERE:([IndexTest].[dbo].[lineitem].[l_commitdate]<[IndexTest].[dbo].[lineitem].[l_receiptdate]) ORDERED FORWARD)
lineitem 테이블에서l_commitdate < l_receiptdate 조건의 아이템전체를 읽어들여 l_orderkey기준으로 그룹핑처리를 합니다.
l_orderkey조건자 기준으로 데이터(스트림)을분할하고 위에서 실행된 Bitmap연산자에 매핑해 데이터를 필터링 합니다.
아래는 온라인설명서에있는 병렬처리 작업흐름을 보여주고 있습니다. 테스트한 스크립트의 작업흐름과는 조인처리 및 정렬처리등이 좀 다릅니다만 대부분의 병렬처리는 parallelism의 Repartition Streams 연산자로 스트림을 분할하고 정렬 및 조인후 Gather Streams 연산자로 결합하는 흐름을 갖고 있습니다.
병렬처리 임계값에 따른 예상실행비용을 확인해 보겠습니다. sp_configure 'show advanced options', 1; GO reconfigure; GO sp_configure 'cost threshold for parallelism', 5; GO reconfigure; GO SET ROWCOUNT 1 SELECT o_orderpriority ,COUNT(*) AS Order_Count FROM orders WHERE o_orderdate >= '2010-11-06 00:00:00.000' AND o_orderdate < '2010-11-06 21:14:19.960' AND EXISTS( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate) GROUP BY o_orderpriority ORDER BY o_orderpriority OPTION (HASH JOIN) SET ROWCOUNT 0 GO 임계값이 5일 경우 위 쿼리를 실행해 예상실행 비용을 확인해 보면 5보다 크므로 병렬처리를 합니다. (이미지의 예상비용은 병렬처리했을시 예상비용입니다. 직렬처리시 아래와 같이 5.8 정도가 나옵니다.) 임계값이 6일 경우 위 쿼리를 실행해 예상실행 비용을 확인해 보면 6보다 작으므로 직렬처리를 합니다.
