1 쿼리플랜비교1:Join

2 쿼리플랜비교2:Star Join

3 쿼리플랜비교3:Partitioning

4 병렬처리 강제화

5 파티션 압축

6 Distribute, Repartition, Gather Stream

7 결론

8 참고자료

테스트 준비

-- 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 버전

2008 버전

2005버전의 경우는 Thread 4를 제외하고는 작업을 하지 않을 테지만, 2008의 경우는 첫 번째 작업을 끝내고 난 결과를 다시 여러 Thread에 분배함으로써 다음 작업을 수월하게 해준다.

테스트 준비

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 버전

2008 버전

조인 횟수를 확 줄여주는 것이 포인트다.

기본

테스트 준비

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 버전

2008 버전

실제실행계획을 xml로 떠서 보면 몇개의 thread가 사용되었으며, 각각의 thread가 몇개의 row를 처리했는지 볼 수 있다. Ctrl+L을 눌러서 나오는 예상실행계획에서는 볼 수 없다. 아래의 그림처럼 <RunTimeInformation> ... </RunTimeInformation> 태그부분을 보면 된다. 표시된 부분을 보면 총 5개의 thread가 사용되었으며, 각각의 thread는 대략 4만5천 row씩을 처리한 것을 볼 수 있다.

이 방법은 문서화되지 않은 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는 힌트로 준 실행계획을 그대로 사용한 것이다. 뭐.. 가끔씩 쓸만할라나?

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

sql server 옵티마이저는 serial vs parallel 비용을 비교해서 둘 중에 작은 것을 선택한다고 한다.

2005보다 병렬처리가 좋아졌다. 알고리즘(처리방식)이 좋아진거지 실제 환경에서 좋아졌다고는 볼 수 없다. 그림을 보면 알겠지만 논리적 읽기 수가 2008 버전이 더 많은 부분이 있다. 이제까지 I/O Base의 성능튜닝 기준이 바뀌었다고 볼 수도 있는 것이다. 병렬처리는 하드웨어 자원을 최대한 많이 쓰고 최대한 효율적으로 처리하기 위한 처리방법이므로 I/O에 너무 집착하지는 말아야 할 것이다. CPU, RAM, Disk 어느 한 곳이라도 병목현상이 발생하면 시스템은 전체적으로 느려진다는 진리는 여전히 유효하다.

• Forcing a Parallel Query Execution Plan
  
• Understanding and Controlling Parallel Query Processing in SQL Server 2008
  
• Understanding and Using Parallelism in SQL Server