컴퓨토피아

김정선의 좋은 글을 찾아서……
SQL Server 2005 파티션 제외(Partition Elimination) 기능

저자: Lubor Kollar, SQLCAT 팀 블로그

원본: http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx

 

김정선(jskim@feelanet.com) 필라넷 DB사업부 수석컨설턴트 SQLServer 아카데미/트라이콤 교육센터 강사   Microsoft SQL Server MVP MCT/MCITP/MCDBA

역자 서문

“김정선의 좋은 글을 찾아서” 그 첫 번째 글이네요 ^^

Vista의 사이드 바에 추가할 블로그 피드를 정리하다가, 오랜만에 sqlcat팀(아시죠? ^^) 블로그까지 가게 되었습니다. 좋은 글이 없나 뒤적거리다가 마침 파티션 관련된 글이 있어서 선정하게 되었습니다. 2006년 2월에 쓰여진 글이네요.

Partition Elimination(역주: 불필요한 파티션을 검색하지 않는)은 파티션 쿼리에서 성능 상에 중요한 이슈가 될 수 밖에 없습니다. 이미 이전의 파티션 뷰에서부터 그래왔으니까요. 관련된 내용이라 더욱 관심이 갔습니다. 그렇지 않아도 요즘 파티션 관련해서 복습을 좀 하고 있거든요.

파티션 써 보셨나요? 얘기 좀 해주세요, 읽지만 마시고 ^^

번역에 대해서 몇 말씀 드리면,

번역은 역시나 역자 마음대로 합니다 ^^. 원문이 있으니 충분히 참고하실 수 있으실 거구요.

제가 여전히 강의나 세미나를 많이 하는 터라, 가급적 용어는 온라인 설명서(“분할”이라고 번역)와 맞추려고 합니다만, 파티션은 워낙 이전부터 통상적으로 써오던 표현이라 그냥 파티션이라고 했습니다.

원문 그대로 번역하지 않고 필요에 따라 생략하거나 추가 언급한 부분도 있습니다. 쉽게 읽으실 수 있도록…

아무튼 좋은 글이라 생각되니, 여러분들에게도 도움이 되셨으면 합니다.

꼭 덧글 달아주실거죠? ^^

본문

파티션 제외 기능은 파티션 테이블이나 파티션 뷰 쿼리를 실행할 때 SQL Server에서 매우 중요한 부분이다. 일반적으로 SQL Server는 조건자에 의해서 제외되는 파티션 영역을 스캔하지 않는 방식으로 이러한 기능을 수행해 낸다. 그런데 최근에 SQL Server 2005의 파티션 테이블에 대해서 이러한 파티션 제외 기능이 제대로 동작하는 않는 시나리오를 발견했다. 여기서 문제가 되는 해당 조건과 그 해결 방안을 소개하고자 한다. 추가로 파티션 쿼리가 이러한 제외 기능을 잘 수행하는지 판단하는 방법도 배우게 될 것이며, 정적 및 동적 파티션 제외가 무엇인지도 배우게 될 것이다. (역주: 사실 이 부분 때문에 번역 글로 선택했습니다. ^^)

파티션 제외가 제대로 수행되는지 확인하는 가장 좋은 방법은 SET STATISTICS PROFILE ON 명령을 사용하는 것(혹은 그래픽 실행 계획)이다, 해당 쿼리를 실행하고 이 명령의 출력 결과를 확인하면 된다.

(역주: 최신 업데이트된 온라인 설명서에는 “Interpreting Execution Plans of Partitioned Objects”라는 주제의 새로운 문서가 포함되어 있습니다. 내용은 짧지만, 한 번 읽어보세요 ^^ )

우선 예제 테이블부터 만들자:

create partition function PF1 (int) as range for values (100,200,300,400);

create partition scheme PS1 as partition PF1 all to ([PRIMARY]);

go

create table t1 (a int, b int) on PS1 (a);

go

declare @i int;

set @i=1;

set nocount on;

while (@i<22)

begin;

insert into t1 values (20*@i, @i);

set @i=@i+1;

end;

go

다음 쿼리는 t1 테이블의 총 5개 파티션에 걸친 데이터의 분포를 알려준다:

select $partition.PF1(a) [Partition Number], a, b from t1

Partition Number       a         b

1                                      20         1

1                                      40         2

1                                      60         3

1                                      80         4

1                                      100       5

2                                      120       6

2                                      140       7

2                                      160       8

2                                      180       9

2                                      200       10

3                                      220       11

3                                      240       12

3                                      260       13

3                                      280       14

3                                      300       15

4                                      320       16

4                                      340       17

4                                      360       18

4                                      380       19

4                                      400       20

5                                      420       21

우선, SQL Server 2005에서 파티션 제외가 정상적으로 수행되는 5개의 예제를 보여줄 것이다. 그리고 정적 및 동적 파티션 제외의 차이를 설명한다. 출력 결과에 대해서도 이후에 확인한다.

set statistics profile on;

declare @i1 int;

declare @i2 int;

set @i1=50;

set @i2=250 ;

select * from t1 where a<50 or  a>450; -- (Q1) 2개 파티션만 스캔

select * from t1 where a in (50,450); -- (Q2) 2개 파티션만 스캔

select * from t1 where a<@i2 and  a>100; -- (Q3) 2개 파티션만 스캔

select * from t1 where a=100;-- (Q4) 1개 파티션만 스캔 – 정적 파티션 제외

select * from t1 where a=@i2; -- (Q5) 1개 파티션만 스캔 – 동적 파티션 제외

set statistics profile off;

각 쿼리의 결과 집합 다음에 showplan 출력(역주: 흔히 말하는 쿼리 (실행) 계획)을 보여준다. 여기서 “Rows” 및 “Executes” 항목을 살펴보자.

쿼리 Q1, select * from t1 where a<50 or  a>450에 대한 결과를 보면:

(역주: 쿼리 계획 상의 주석은 번역하지 않았습니다)

Rows     Executes             StmtText

2           1           select * from t1 where a<50 or  a>450; -- (Q1) only two partitions

2           1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

2           1                  |--Constant Scan(VALUES:(((1)),((5))))

2           2                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

파티션 테이블의 스캔(검색)은 파티션에 Nested Loop을 반복하고 있다. 그에 앞서 Constant Scan 연산자에서 파티션 1과 5만을 스캔할 것임을 알 수 있다. “Executes” 값 2는 개별 파티션에 대해 2번 스캔했음을 확인시켜준다.

쿼리 Q2의 IN 절 “a in (50, 450)”는 “a = 50 OR a = 450”으로 변환되고, 1과 5 두 파티션만 스캔하는 것을 보여준다.

Rows     Executes             StmtText

0           1           select * from t1 where a in (50,450); -- (Q2) only two partitions are sc

0           1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

2           1                  |--Constant Scan(VALUES:(((1)),((5))))

0           2                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[dbo].[t

세 번째 쿼리 Q3,

select * from t1 where a<@i2 and  a>100

의 결과는

Rows     Executes             StmtText

7           1           select * from t1 where a<@i2 and  a>100; -- (Q3) only two partitions

7           1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

2           1                  |--Filter(WHERE:([PtnIds1004]<=RangePartitionNew([@i2],(0)

4           1                  |    |--Constant Scan(VALUES:(((2)),((3)),((4)),((5))))

7           2                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

Constant Scan 연산자를 보면, 우선 정적으로 파티션 1이 제외된 것을 알 수 있다. 이는 “a > 100”이라는 조건절 때문에 가능한 것이다. 그리고 그 위에 Filter 연산자의 내용을 보면 “a < @i2” 조건자를 사용해서 암시적으로 보다 많은 파티션을 제외하고 있다. 후자가 바로 동적 제외이다. 이는 제외할 파티션을 결정하는데 사용될 로컬변수 @i2의 값을 컴파일 타임이 아닌 런타임에 알 수 있기 때문이다. 실제로 Table Scan 연산자의 “Executes” 항목을 보면 2번만 스캔하고 있음을 알 수 있다.

쿼리 Q4

select * from t1 where a=100

앞서 4개의 쿼리와 달리 이 쿼리는 Constant Scan 연산자가 없다. 이는 SQL Server가 컴파일 타임에 하나의 파티션만 필요하다는 것을 알 수 있기 때문이다. 아래 쿼리 계획을 보면

Rows     Executes             StmtText

1           1           SELECT * FROM [t1] WHERE [a]=@1

1           1             |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[dbo]. [t1].[a]=(100)) PARTITION ID:((1)))

이를 쿼리 Q5와 비교해 보면

select * from t1 where a=@i2.

물론 최종 실행은 하나의 파티션만 스캔하고 있지만 컴파일 타임에는 로컬변수 값을 알 수 없다. 따라서 WHERE절이 매개변수로 처리되고 있다.

Rows     Executes             StmtText

0           1           select * from t1 where a=@i2; -- (Q5) only one partition is scanned 0          1             |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[dbo].[t1].[a]=[@i2]) PARTITION ID:(RangePartitionNew([@i2],(0),(100),(200),(300),(400))))

위 예제는 SQL Server 파티션 제외 기능이 제대로 수행되고 있음을 보여준다.

이제 다음의 경우를 살펴보자

set statistics profile on;

declare @i1 int;

declare @i2 int;

set @i1=50;

set @i2=250;

select * from t1 where a<50 or  a>@i2; -- (Q6)

select * from t1 where a<@i1 or  a>@i2; -- (Q7)

select * from t1 where a in (@i1,@i2); -- (Q8)

set statistics profile off;

경계 값(100, 200, 300, 400)과 쿼리의 로컬변수 값 @i1=50, @i2=250을 따져 보면, 위 쿼리 Q6와 Q7은 두 번째 파티션(200미만)을 제외할 수 있다고 판단된다. 그런데, Q6와 Q7의 쿼리 계획을 보면,

-- Q6

Rows     Executes             StmtText

11         1           select * from t1 where a<50 or  a>@i2 -- (Q6) all partitions are

11         1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

5           1                  |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))

11         5                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

-- Q7

Rows     Executes             StmtText

11         1           select * from t1 where a<@i1 or  a>@i2 -- (Q7) all partitions are

11         1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

5           1                  |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))

11         5                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

쿼리 Q8의 경우는 파티션 1과 3만을 스캔하면 된다. 그런데 또 5개 모두 스캔하고 있다.

Rows     Executes             StmtText

0           1           select * from t1 where a in (@i1,@i2) -- (Q8)

0           1             |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PAR

5           1                  |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))

0           5                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

SQL Server 2005에서 두 가지의 경우 모든 파티션을 스캔하고 있다. “SQL Server에서의 파티션 제외 기능은 제외식을 가진 조건자 사이에 OR가 있고 그 중 매개변수화가 되는 경우 제대로 동작하지 않는다.”

IN 조건자 또한 OR 조건자로 변환될 것이고 하나가 매개 변수 값을 가지므로 이 조건에 만족한다.

(역주: 이 글의 답글을 보면, 유사 케이스도 있습니다)

해결 방법은 여러 가지가 존재한다. 가장 편한 방법은 OR 대신에 UNION ALL을 사용하는 것이다. Q6의 경우라면

select * from t1 where a<50

UNION ALL

select * from t1 where a>@i2

쿼리 계획은

Rows     Executes             StmtText

11         1           select * from t1 where a<50   UNION ALL  select * from t1 where a

11         1             |--Concatenation

2           1                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

9           1                  |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1011]

3           1                       |--Filter(WHERE:([PtnIds1011]>=RangePartitionNew([@i2

5           1                       |    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4))

9           3                       |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([tes

쿼리 계획을 보면 조건자 “a < 50”에 대해서는 정적 제외를 하고 “a > @i2”에 대해서는 동적 파티션 제외가 수행됨을 알 수 있다. 첫 번째 것은 단일 파티션만 처리하고 두 번째(a >  250)에서 이후 3개의 파티션을 스캔할 것이다.

두 번째 쿼리 Q7은 다음과 같이 재 작성할 수 있다.

select * from t1 where a<@i1

UNION ALL

select * from t1 where a>@i2

변경된 쿼리 계획은(역주: 예상하겠지만 ^^)                 

Rows     Executes             StmtText

11         1           select * from t1 where a<@i1   UNION ALL  select * from t1 where

11         1             |--Concatenation

2           1                  |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1010]

1           1                  |    |--Filter(WHERE:([PtnIds1010]<=RangePartitionNew([@i1

5           1                  |    |    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4))

2           1                  |    |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([tes

9           1                  |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1011]

3           1                       |--Filter(WHERE:([PtnIds1011]>=RangePartitionNew([@i2

5           1                       |    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4))

9           3                       |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([tes

세 번째 쿼리 Q7 또한 다음과 같다.

select * from t1 where a =@i1    

UNION ALL

select * from t1 where a =@i2

역시 실행 계획도

Rows     Executes             StmtText

0           1           select * from t1 where a =@i1   -- (Q7)   UNION ALL  select * fro

0           1             |--Concatenation

0           1                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

0           1                  |--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[d

(역주: OR 문제를 해결하는데 많은 사용되는 것이 UNION ALL이죠! 이유는? 실행 계획을 명시적으로 분리할 수 있으니까! 그런데 이 방법 밖에 없나?, 그럼 안되는데…^^ )

또는 동적 쿼리 형태로 문자열로 쿼리를 결합해서 결합된 쿼리를 수행하도록 처리할 수도 있다.

(역주: 만일 쿼리를 UNION로 바꿀 수도 없고, 동적 쿼리로 바꾸기에 너무 큰(긴) 쿼리라면 어떻게 해야 하나?...여러분 또 다른 방법을 찾아주세요~~~ help me~~~)

요약

SQL Server에서 파티션 제외 기능을 확인하는 방법을 살펴봤다. 대부분의 경우 SQL Server가 이를 훌륭히 처리해 주겠지만, 위에서 소개된 사례와 같이 제대로 수행되지 못하는 경우도 발생한다. 이런 경우에 반드시 쿼리 계획을 확인하고, 해당 쿼리가 정적 혹은 동적 파티션 제외가 제대로 수행되는지 확인해야 한다. SQL Server 개발 팀은 향후 서비스 팩이나 릴리스에서 위에서 언급한 문제들을 해결할 계획을 세우고 있다.

(역주: 현재 SP2, 9.00.3042 버전에서 테스트한 결과 아직 미 반영 ^^, SQL Server 2008은 어떨까요? ^^)