Contents

1 貎朱Μ觜蟲1:Join
2 貎朱Μ觜蟲2:Star Join
3 貎朱Μ觜蟲3:Partitioning
4 覲豌襴 螳
5 一 豢
6 Distribute, Repartition, Gather Stream
7 蟆磯
8 谿瑚襭


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/

覓語 伎 SQL Server Magazine 2010 7語 螳 伎 覦 螳 蟆企. 螳螳 2005(sp3), 2008(sp1) 覯 SQL Server螳 れ ろ 蟆曙 . ( 覓語 2008R2 ろ誤.) れ 貊襯 2005, 2008 螳 覯 語ろ伎れ ろ.

1 貎朱Μ觜蟲1:Join #

ろ 譴觜
-- Sample datatabase
SET NOCOUNT ON;
IF DB_ID('testparallel') IS NULL CREATE DATABASE testparallel;
GO

USE testparallel;
GO
-- Helper function GetNums
-- returns a sequence of integers of a requested size
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL
DROP FUNCTION dbo.GetNums;
GO

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

USE testparallel;
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2') IS NOT NULL DROP TABLE dbo.T2;
GO

CREATE TABLE dbo.T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT(0x)
);

CREATE UNIQUE CLUSTERED INDEX idx_cl_col1 ON dbo.T1(col1);

INSERT INTO dbo.T1 WITH(TABLOCK) (col1, col2)
SELECT n AS col1, n AS col2
FROM dbo.GetNums(1000000);

SELECT * INTO dbo.T2 FROM dbo.T1;
CREATE UNIQUE CLUSTERED INDEX idx_cl_col1 ON dbo.T2(col1);
GO

れ SQL 2005, 2008 螳 語ろ伎れ ろ貅覲伎.
SELECT *
FROM dbo.T1
	JOIN T2 
		ON T1.col1 = T2.col1
WHERE T1.col2 <= 100;

2005 覯
p01.jpg

2008 覯
p02.jpg

2005覯 蟆曙磯 Thread 4襯 誤螻 讌 讌襷, 2008 蟆曙磯 豌 覯讌 願 蟆郁骸襯 れ Thread 覿覦壱朱 れ 蟆 伎.

2 貎朱Μ觜蟲2:Star Join #

ろ 譴觜
USE testparallel;
IF OBJECT_ID('dbo.Fact', 'U') IS NOT NULL DROP TABLE dbo.Fact;
IF OBJECT_ID('dbo.Dim1', 'U') IS NOT NULL DROP TABLE dbo.Dim1;
IF OBJECT_ID('dbo.Dim2', 'U') IS NOT NULL DROP TABLE dbo.Dim2;
IF OBJECT_ID('dbo.Dim3', 'U') IS NOT NULL DROP TABLE dbo.Dim3;
GO

CREATE TABLE dbo.Dim1
(
key1 INT NOT NULL CONSTRAINT PK_Dim1 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);

CREATE TABLE dbo.Dim2
(
key2 INT NOT NULL CONSTRAINT PK_Dim2 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);

CREATE TABLE dbo.Dim3
(
key3 INT NOT NULL CONSTRAINT PK_Dim3 PRIMARY KEY,
attr1 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x)
);

CREATE TABLE dbo.Fact
(
key1 INT NOT NULL CONSTRAINT FK_Fact_Dim1 FOREIGN KEY
REFERENCES dbo.Dim1,key2 INT NOT NULL CONSTRAINT FK_Fact_Dim2 FOREIGN KEY
REFERENCES dbo.Dim2,
key3 INT NOT NULL CONSTRAINT FK_Fact_Dim3 FOREIGN KEY REFERENCES dbo.Dim3,
measure1 INT NOT NULL,
measure2 INT NOT NULL,
measure3 INT NOT NULL,
filler BINARY(100) NOT NULL DEFAULT (0x),
CONSTRAINT PK_Fact PRIMARY KEY(key1, key2, key3)
);

INSERT INTO dbo.Dim1(key1, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 20 + 1
FROM dbo.GetNums(100);

INSERT INTO dbo.Dim2(key2, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 10 + 1
FROM dbo.GetNums(50);

INSERT INTO dbo.Dim3(key3, attr1)
SELECT n, ABS(CHECKSUM(NEWID())) % 40 + 1
FROM dbo.GetNums(200);

INSERT INTO dbo.Fact WITH (TABLOCK)
(key1, key2, key3, measure1, measure2, measure3)
SELECT N1.n, N2.n, N3.n,
ABS(CHECKSUM(NEWID())) % 1000000 + 1,
ABS(CHECKSUM(NEWID())) % 1000000 + 1,
ABS(CHECKSUM(NEWID())) % 1000000 + 1
FROM dbo.GetNums(100) AS N1
CROSS JOIN dbo.GetNums(50) AS N2
CROSS JOIN dbo.GetNums(200) AS N3;
GO

れ SQL 2005, 2008 螳 語ろ伎れ ろ貅覲伎. (Star Join 谿瑚)
SELECT *
FROM dbo.Fact AS F
	JOIN dbo.Dim2 AS D2
		ON F.key2 = D2.key2
	JOIN dbo.Dim3 AS D3
		ON F.key3 = D3.key3
WHERE D2.attr1 <= 3
AND D3.attr1 <= 2;

2005 覯
p03.jpg

2008 覯
p04.jpg

譟一 襯 譴譯朱 蟆 誤碁.

3 貎朱Μ觜蟲3:Partitioning #

蠍磯蓋
partition_2005_2008.png

ろ 譴觜

USE testparallel;

IF OBJECT_ID('dbo.PartitionedTable', 'U') IS NOT NULL
DROP TABLE dbo.PartitionedTable;

IF EXISTS(SELECT * FROM sys.partition_schemes WHERE name = 'PS1')
DROP PARTITION SCHEME PS1;

IF EXISTS(SELECT * FROM sys.partition_functions WHERE name = 'PF1')
DROP PARTITION FUNCTION PF1;
GO

CREATE PARTITION FUNCTION PF1 (INT)
AS RANGE LEFT FOR VALUES (250000, 500000, 750000);

CREATE PARTITION SCHEME PS1
AS PARTITION PF1 ALL TO ([PRIMARY]);
CREATE TABLE dbo.PartitionedTable

(
col1 INT NOT NULL,
col2 INT NOT NULL,
filler BINARY(100) DEFAULT(0x)
) ON PS1(col1);

CREATE UNIQUE CLUSTERED INDEX idx_col1 ON dbo.PartitionedTable(col1) ON PS1(col1);

INSERT INTO dbo.PartitionedTable WITH (TABLOCK) (col1, col2)
SELECT n, ABS(CHECKSUM(NEWID())) % 1000000 + 1
FROM dbo.GetNums(1000000);

れ SQL覓語 2005, 2008 螳螳 語ろ伎れ ろ貅覲碁.
SELECT *
FROM dbo.PartitionedTable
WHERE col1 <= 500000
ORDER BY col2;

2005 覯
p05.jpg

2008 覯
p06.jpg


れろ螻 xml襦 覲企 覈螳 thread螳 朱, 螳螳 thread螳 覈螳 row襯 豌襴讌 覲 . Ctrl+L る ろ螻 覲 . 蠏碁殊 <RunTimeInformation> ... </RunTimeInformation> 蠏碁覿 覲企 . 覿覿 覲企 豐 5螳 thread螳 朱, 螳螳 thread 4襷5豌 row 豌襴 蟆 覲 .
xml_plan.jpg

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 碁 譴 ろ螻 蠏碁襦 蟆企. 覘.. 螳 碁朱?

5 一 豢 #

set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)
,   @p int
,   @sql varchar(4000)

set @bdt = '20110928'

while (@bdt <= '20300301')
begin
	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)
    set @p = $partition.一(@bdt)

    set @sql = '
    alter table 企覈
    rebuild partition = ' + convert(varchar, @p) + '
    with (data_compression = page)'
    exec (@sql)

    print @sql
	--print @bdt + ', ' + @edt
	set @bdt = @edt
end

6 Distribute, Repartition, Gather Stream #

sql server 牛磯伎 serial vs parallel 觜 觜蟲伎 譴 蟆 り .
parallel_mathod_2.png

7 蟆磯 #

2005覲企 覲豌襴螳 譬譟. 螻襴讀(豌襴覦) 譬讌蟇一 れ 蟆曙 譬譟り 覲 . 蠏碁殊 覲企 蟆讌襷 朱Μ 所鍵 螳 2008 覯 襷 覿覿 . 伎蟾讌 I/O Base 焔ロ 蠍一 覦り 覲 蟆企. 覲豌襴 豕 襷 郁 豕 朱 豌襴蠍 豌襴覦覯企襦 I/O 覓 讌谿讌 襷 蟆企. CPU, RAM, Disk 企 螻褐企朱 覲覈 覦覃 ろ 豌伎朱 れる 讌襴 .

8 谿瑚襭 #