Contents

1 蠍磯蓋
2 覲豌襴 螻螳 譟一
3 覲豌襴
4 覲豌襴 螳
5 2016 SP1 CU3+
6 谿瑚襭


sql server 2016 sp1(cu3) 危 覯 ENABLE_PARALLEL_PLAN_PREFERENCE 碁ゼ 磯 .
https://www.mssqltips.com/sqlservertip/4939/how-to-force-a-parallel-execution-plan-in-sql-server-2016/

2005, 2008 覯 覲豌襴 Star Join 一一 殊. 伎 伎 磯 瑚襦 蟆.

1 蠍磯蓋 #

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)
pararell01.jpg

2 覲豌襴 螻螳 譟一 #

MSSQL Server 覲貎朱Μ襯 企手 覲 . 蠏碁覩襦 覿覿 Merge Join Hash Join朱 貎朱Μ 語企. 覲貎朱Μ 螳螳 CPU螳 曙 覿覿 曙 Merge Join 蠍 伎 ろ碁 一 覲蟆渚螻, Sort Merge Join . ろ碁殊 讌螻, 讌 螻殊 . 企 碁 豌襴襯 蟆企. 蠏碁 朱 OLTP 蟆曙 覲 貎朱Μ 譯 覈蟆る SQL 蟆願, SQL 螻 覲 貎朱Μ 覈蟆る り覓語襦 誤 蟆企. 覿覿 OLTP 覓企れ 碁 蠍郁 譯 . 蠏碁覩襦 讌貎朱Μ螳 覿覿 襴. 覓企Μ SQL 企 覲 貎朱Μ 螻豎讌讌 る れ螻 螳 碁ゼ 伎 .

SELECT 
FROM 
OPTION (MAXDOP 1)

れ 覦覯朱 れ 蠏碁手骸 螳 리 병렬 실행의 경우 최소 쿼리 계획 임계값 譟一 覦覯企. 襦 れ覃 貎朱Μ 觜 100 願讌 朱 覲貎朱Μ 讌 . 覲貎朱Μ螳 譯 る 覲貎朱Μ 襦語 襯 蟆 覲 貎朱Μ 譴 るジ レ 覩語讌 襦 覦覯企.

pararell02.jpg

螻螳 れ螻 螳 譟一 .
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'

3 覲豌襴 #

蟆渚朱 れ螻 螳 蟆曙郁 覲豌襴 語 .

  1. 貎朱Μ 覈詞О
  2. ろ螻 觜 れ螳(蠍磯蓋 5)覲企 . (牛磯伎 覈視 觜一)
  3. 企 語 譟磯 蠑語.
  4. 貎朱Μ ろ 觜 .

2008覯 蟆曙磯 Parallelism Enhancements in SQL Server 2008 覓語襯 谿瑚蠍 覦.

4 覲豌襴 螳 #

覦覯 覓語讌 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
p01.png

蠏碁殊 覲企 蟆讌襷 覲豌襴 讌 . 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
p02.png

蟆曙磯 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 (谿曙 企)

p03.png

轟危 碁Μ 觜 5襯 讌 る 蟆企. 伎蟇 sql server 碁 譴 ろ螻 蠏碁襦 蟆企. 覘.. 螳 碁朱? DW 譟磯 碁.

5 2016 SP1 CU3+ #

OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

6 谿瑚襭 #