Partition Elimination in SQL Server 2005

Lubor Kollar

 

Partition elimination is very important when SQL Server executes queries against partitioned tables or partitioned views. In general, SQL Server is doing an excellent job of not scanning the partitions that are excluded by some predicates. Recently we have discovered one scenario where the partition elimination does not work against partitioned tables in SQL Server 2005 and this blog describes the conditions leading to the problem as well as easy workarounds. Additionally you will learn how to discover if partition elimination works for your query or not. You will also learn what is static and dynamic partition elimination.

 

The most reliable way to find out if partition elimination happens in your query is to use the SET STATISTICS PROFILE ON command, run the query and investigate the output. But let me start with building our example table:

 

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

 

The following query shows distribution of all rows in table t1 across the five partitions:

 

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

 

First, I will show 5 examples how partition elimination works correctly in SQL Server 2005 and I will explain the difference between the static and dynamic partition elimination. Here is a small batch and we will investigate the output later below

 

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) only two partitions are scanned

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

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

select * from t1 where a=100;-- (Q4) only one partition is scanned - static partition elimination

select * from t1 where a=@i2; -- (Q5) only one partition is scanned - dynamic partition elimination

set statistics profile off;

You will see the result set followed by the showplan with the columns “Rows” and “Executes” in front of it for each of the four queries above. For the query Q1

select * from t1 where a<50 or a>450

the showplan output is

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

The scan of the partitioned table becomes a Nested Loops looping over the partitions. And we see already in the Constant Scan that we will be scanning only partitions 1 and 5. The “Executes” value 2 below confirms we did 2 scans of an individual partition.

The IN predicate “a in (50,450)” in the Q2 is turned into “ a = 50 OR a = 450”, and SQL Server will access only the two partitions, 1, and 5, containing all qualifying rows

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

The second query, Q3,

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

yields

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

In the above plan we see that we have statically eliminated the partition 1 from the Constant Scan because of the a>100 predicate and we are using the predicate a<@i2 to potentially eliminate more partitions using the Filter above the Constant Scan. The later is dynamic elimination because it depends on the run t