Microsoft SQL Server Development Customer Advisory Team : Partition Elimination in SQL Server 2005

News

Visit the SQL Server Best Practices site (http://sqlcat.com) for real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level.

Archives

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]);

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

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;

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 time value of the @i2 how many additional partitions will be eliminated. By looking at the “Executes” of the Table Scan we see that again we are scanning only 2 individual partitions.

The query Q4

select * from t1 where a=100

has no Constant Scan at all. This is because SQL Server knows already in the compile time which single partition will be accessed to retrieve the complete result. Here is the plan

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)))

Now compare the above with the plan for query Q5

select * from t1 where a=@i2.

Also here we know we will scan only a single partition but we don’t know which at the time we compile the query therefore the WHERE is paramaterized

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))))

All the above partition elimination worked as expected and we saw that SQL Server skipped as many partitions as possible. Now investigate the following cases

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;

Taking into consideration the boundary values (100,200,300,400) and the values of @i1=50 and @i2=250, both the Q6 and Q7 should be able to skip the second partition safely. However, the plans are

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

and

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

The Q8 should scan only two partitions – 1 and 3 – but it will scan all five again

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

In both cases SQL Server 2005 is scanning all partitions. The partition elimination in SQL Server 2005 does not work if there is OR between the eliminating predicates and if at least one of them is parameterized at the same time. Since the IN predicate with at least 2 elements inside the list is transformed to an OR predicate, the same is true for the IN lists with at least one parameter value as well.

There are several possible workarounds, and I will show the most convenient one with the UNION ALL replacing the OR. In our case the query Q6 will become

select * from t1 where a<50

UNION ALL

select * from t1 where a>@i2

with the query plan

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

The query plan above has static elimination for the predicate a<50 and dynamic partition elimination for the predicate a>i2. The first one will end up accessing a single partition and the second will scan the last three partitions because of the @i2 value of 250.

The second query, Q6, will be rewritten to

select * from t1 where a<@i1

UNION ALL

select * from t1 where a>@i2

with the query plan

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

And the third query, Q7, is equivalent to

select * from t1 where a =@i1

UNION ALL

select * from t1 where a =@i2

with the query plan

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

You can also use dynamic query to remove the parameters by converting them to strings and concatenating with the rest of the query. In SQL Server 2000 and before such use of dynamic query inside of a stored procedure would cause authentication failure for users of the stored procedure without explicit access to the objects referenced in the dynamic query but in SQL Server 2005 this problem may be avoided by using the EXECUTE AS clause that can implicitly define the execution context of the statement.

Summary

I have shown how you can find out if SQL Server eliminates partitions in your queries. Most of the time SQL Server is doing great job in partition elimination and it is scanning or seeking only partitions that could potentially yield rows satisfying the WHERE, IN or other row eliminating clauses. In spite of that you should still check the query plans of your queries against partitioned tables if you suspect the performance may be or is an issue. You should be aware that SQL Server is employing both the static partition elimination when the query is optimized and dynamic partition elimination when the choice of scanned and skipped partitions is made at the query execution time.

I have shown an example, when there is at least one parameter and either IN or OR clause when SQL Server is not eliminating all partitions it could. I’m showing a workaround using a UNION ALL instead of the OR and SQL Server is again eliminating all partitions that cannot yield any row satisfying the query. SQL Server development team is planning to address the above mentioned partition elimination problem in one of the future SQL Server service packs or releases.

Posted: Friday, February 17, 2006 10:15 PM by LuborK

Filed under: Performance and Scalability, Development & Programming

Comments

AssafF said:

Very good article.
The last comment about execute as is not so clear. I suggest putting the comment in different paragraph.

# March 16, 2006 3:50 AM

DannyRa said:

great article, but what puzzeles me is

i have manged to create the following repro that demonstrates a partition elimination problem with 'OR'
and there is NO paramtrization in the queries ,this repro is from our production environment
and the partitioned table actually contains 100 million rows ,but the query plans are the same even with empty table,the repro works both on RTM and SP1 MARCH CTP

/****** Object: PartitionFunction [YearRange] Script Date: 03/23/2006 16:36:25 ******/
CREATE PARTITION FUNCTION [YearRange](smallint)
AS RANGE RIGHT FOR VALUES (1995, 1996, 1997, 1998, 1999, 2000,
2001, 2002, 2003, 2004, 2005, 2006, 2007)
GO
/****** Object: PartitionScheme [YEARS_PS_SHR] Script Date: 03/23/2006 16:36:01 ******/
CREATE PARTITION SCHEME [YEARS_PS_SHR]
AS PARTITION [YearRange]
ALL TO ([PRIMARY])
GO

/****** Object: Table [dbo].[bth_fact] Script Date: 03/23/2006 16:39:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bth_fact](
[rowid] [bigint] NOT NULL,
[teudat_zehut_src] [varchar](10) COLLATE Hebrew_CI_AS NOT NULL,
[teudat_zehut] varchar(10) NOT NULL,
[kod_beit_ch] [int] NOT NULL,
[kod_kupa] [tinyint] NOT NULL,
[kod_peula_bdika] [varchar](7) COLLATE Hebrew_CI_AS NOT NULL,
[kod_peula_old] [varchar](7) COLLATE Hebrew_CI_AS NULL,
[kod_sug_sherut] [tinyint] NOT NULL,
[kod_machl_mirp] [int] NOT NULL,
[shnat_chiuv]smallint NOT NULL,
[chodesh_chiuv] tinyint NOT NULL,
[rivon_chiuv] tinyint NOT NULL,
[date_knisa] [smalldatetime] NOT NULL,
[date_shichrur] [smalldatetime] NULL,
[kmt_yamim_tipulim] [smallint] NOT NULL,
[alut] [decimal](11, 2) NOT NULL,
[tofes17] [varchar](9) COLLATE Hebrew_CI_AS NULL,
[mispar_ishp_bikur] [bigint] NOT NULL,
[kod_machl_mirp_src] [varchar](5) COLLATE Hebrew_CI_AS NULL,
[zihui_ishpuz_bikur] [bigint] NOT NULL,
[kod_sug_kabala] [tinyint] NULL,
[kod_sug_knisa] [tinyint] NULL,
[kod_sug_shichrur] [tinyint] NOT NULL,
[kod_sug_chole] [char](1) COLLATE Hebrew_CI_AS NOT NULL,
[kod_sug_cheshbon] [tinyint] NOT NULL,
[kod_kart_taktziv] [tinyint] NOT NULL,
[kod_mosad_memamen] [smallint] NOT NULL,
[gil] [smallint] NOT NULL,
[kod_machoz_beit_ch] tinyint NOT NULL,
[kod_seif_tkz_new] [int] NOT NULL,
[kod_seif_taktzivi] [int] NOT NULL,
[alut_hanacha] [decimal](11, 2) NOT NULL,
[mispar_peul_diff] [smallint] NOT NULL,
[hist_gk] [int] NOT NULL,
[shem_prati] [varchar](15) COLLATE Hebrew_CI_AS NULL,
[shem_mishpacha] [varchar](19) COLLATE Hebrew_CI_AS NULL,
[kod_kart_tkz_new] [tinyint] NOT NULL,
[kod_machoz_mevu] tinyint NOT NULL,
[kod_peula_bdk_src] [varchar](6) COLLATE Hebrew_CI_AS NULL,
[kod_sug_tz] [tinyint] NOT NULL,
[sw_global] [char](1) COLLATE Hebrew_CI_AS NOT NULL,
[minun] [int] NULL,
[peula_mb_gk] [int] NOT NULL,
[kod_bth_simul] [int] NOT NULL,
[kod_source] [tinyint] NOT NULL,
[shnat_knisa] [smallint] NOT NULL,
[kod_machoz_memamen] [tinyint] NOT NULL,
[kod_makor] [tinyint] NOT NULL,
CONSTRAINT [PK_bth_fact] PRIMARY KEY NONCLUSTERED
(
[shnat_chiuv] ASC,
[chodesh_chiuv] ASC,
[rowid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [YEARS_PS_SHR]([shnat_chiuv])
) ON [YEARS_PS_SHR]([shnat_chiuv])

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[bth_fact] WITH CHECK ADD CONSTRAINT [CK_bth_fact_KODS]
CHECK (([kod_source]=(1)))

-------------------

---QUERY:

---1.good plan

SET STATISTICS PROFILE ON

select teudat_zehut,alut_hanacha
from
dbo.bth_fact bth
where
bth.shnat_chiuv in (2003,2005)

---showplan outpt

0 1 select teudat_zehut,alut_hanacha from dbo.bth_fact bth where bth.shnat_chiuv in (2003,2005) 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.006573417 NULL NULL SELECT 0 NULL
0 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003]) NULL 1 0 4.18E-06 25 0.006573417 [bth].[teudat_zehut], [bth].[alut_hanacha] NULL PLAN_ROW 0 1
2 1 |--Constant Scan(VALUES:(((10)),((12)))) 1 3 2 Constant Scan Constant Scan VALUES:(((10)),((12))) NULL 2 0 2.157E-06 11 2.157E-06 [PtnIds1003] NULL PLAN_ROW 0 1
0 2 |--Table Scan(OBJECT:([AdventureWorks].[dbo].[bth_fact] AS [bth]), WHERE:([AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2003) OR [AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2005)) PARTITION ID:([PtnIds1003])) 1 6 2 Table Scan Table Scan OBJECT:([AdventureWorks].[dbo].[bth_fact] AS [bth]), WHERE:([AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2003) OR [AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2005)) PARTITION ID:([PtnIds1003]) [bth].[teudat_zehut], [bth].[shnat_chiuv], [bth].[alut_hanacha] 1 0.003125 0.0001581 27 0.0034412 [bth].[teudat_zehut], [bth].[shnat_chiuv], [bth].[alut_hanacha] NULL PLAN_ROW 0 2

---2. bad plan

select teudat_zehut,alut_hanacha
from
dbo.bth_fact bth
where
(bth.shnat_chiuv =2003 and bth.chodesh_chiuv in (9,10,11))
or (bth.shnat_chiuv = 2005 )

---showplan outpt

0 1 select teudat_zehut,alut_hanacha from dbo.bth_fact bth where (bth.shnat_chiuv =2003 and bth.chodesh_chiuv in (9,10,11)) or (bth.shnat_chiuv = 2005 ) 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.04802274 NULL NULL SELECT 0 NULL
0 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003]) NULL 1 0 4.18E-06 28 0.04802274 [bth].[teudat_zehut], [bth].[alut_hanacha] NULL PLAN_ROW 0 1
14 1 |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)))) 1 3 2 Constant Scan Constant Scan VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14))) NULL 14 0 1.4157E-05 11 1.4157E-05 [PtnIds1003] NULL PLAN_ROW 0 1
0 14 |--Table Scan(OBJECT:([AdventureWorks].[dbo].[bth_fact] AS [bth]), WHERE:([AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2003) AND ([AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(9) OR [AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(10) OR [AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(11)) OR [AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2005)) PARTITION ID:([PtnIds1003])) 1 18 2 Table Scan Table Scan OBJECT:([AdventureWorks].[dbo].[bth_fact] AS [bth]), WHERE:([AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2003) AND ([AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(9) OR [AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(10) OR [AdventureWorks].[dbo].[bth_fact].[chodesh_chiuv] as [bth].[chodesh_chiuv]=(11)) OR [AdventureWorks].[dbo].[bth_fact].[shnat_chiuv] as [bth].[shnat_chiuv]=(2005)) PARTITION ID:([PtnIds1003]) [bth].[teudat_zehut], [bth].[alut_hanacha] 1 0.003125 0.0003038857 28 0.0073794 [bth].[teudat_zehut], [bth].[alut_hanacha] NULL PLAN_ROW 0 14

Any Idea why tis happens ?
Danny Ravid
Ness Technologies
danny.ravid@ness.com

# March 23, 2006 4:33 PM

LuborK said:

Answer to Assaf: Sorry, maybe I was too terse about the "EXECUTE AS". I hope the following explanation from the Kalen Delaney's article in SQL Magazine (http://www.sqlmag.com/Article/ArticleID/42031/42031.html) explains it:
EXECUTE AS can be useful when your module uses dynamic SQL. SQL Server 2000 always checks permissions for each dynamic SQL statement you execute, ignoring condition number 1. (Ownership chaining never applies.) If the caller of the procedure doesn't have permission on the objects referenced in the dynamic SQL, the execution fails. However, in SQL Server 2005, if a user created the procedure containing the dynamic SQL to run in the context of a user who has permission on the referenced objects, execution succeeds.

# March 26, 2006 8:05 AM

LuborK said:

Answer to Danny:
Danny, thanks a lot for all the details. You are correct - this is another case where SQL Server 2005 does not perform the partition elimination. Luckily the same UNION ALL trick described in my blog works again. So please rewrite your quey as follows:
select teudat_zehut,alut_hanacha
from
dbo.bth_fact bth
where
(bth.shnat_chiuv =2003 and bth.chodesh_chiuv in (9,10,11))
UNION ALL
select teudat_zehut,alut_hanacha
from
dbo.bth_fact bth
where
(bth.shnat_chiuv = 2005 )

# March 26, 2006 8:17 AM

Craig Freedman's WebLog said:

In this post, I’m going to take a look at how query plans involving partitioned tables work. Note that

# November 27, 2006 3:12 PM

Sharepoint Experiences :: Brazilian SPS MVP said:

Hi, Here in Brazil I´m working on MS Gold Certified Partner, and I act in two distinct teams - Data...

# February 7, 2007 2:40 PM

EricWelsh said:

Hi,

Great aritical, though do you happen to know why the optimizer it NOT grabbing the correct partition when a @varible is used verses a direct value.

funny thing is the plan doesn't change between estimated verses actual( you would think it would pick up the correct partition after the exection) in addtional if this actually by design out can you verify that the optimizer really is pulling the correct partition.

# April 18, 2007 4:36 PM

LuborK said:

The optimizer cannot grab the correct partition since it does not know the value of the @variable at compile time. You are correct that the correct partition is picked up during execution. The plan stays the same during the execution - it is read only structure used to navigate the execution similarly like your C# or VB program is - only the flow of execution may differ from execution to execution. You are also correct that we could potentially explicitly tell what partition has been visited in the showplan's "run time information" (read chapter 2 of "T-SQL Querying" from the "Inside SQL Server 2005 book if interested about more details" but you can still figure it out from there implicitly because only one branch is executed.

# April 18, 2007 5:39 PM

sholliday said:

Lubor:

declare @i1 int;

set statistics profile on;

set @i1=120;

select * from t1 where a < @i1;

Any reason why the the “Executes” value is 2 here, and not 1?

120 is the first value in Partition#2, thus <120 should only be Partition#1...

Thanks for the post, it has provided some much needed direction with Partitioned Tables.

....

# October 3, 2007 11:27 PM

LuborK said:

I'm not sure what partition function you have in mind... In the one I have in the blog the first boundary value is 100 thus precicate <120 must scan the first two partitions. Or post your Partition Function, Schema, and table to see what you exacly mean.

# October 4, 2007 11:25 AM

sholliday said:

Ok, I see it now.

Its the boundary, now the rows which satisfy.

Partition Number a b

1 100 5

2 120 6

I was thinking (errantly) thinking that because there were only rows which satisfied the <120 condition, it would only scan Part1. But now I see it has to scan Part2 for 101-119 as well (My conditition overlapping with the boundary definition).

Thanks for the followup.

# October 4, 2007 1:39 PM

sholliday said:

I think it would be interested to see the break down of these types of situations:

set statistics profile on;

declare @holder table ( hValue int )

insert into @holder (hValue) values ( 20 )

insert into @holder (hValue) values ( 40 )

insert into @holder (hValue) values ( 60 )

--Derived Table Example

select a, b from t1

join ( select hValue as HV from @holder h ) as derived1

on t1.a = derived1.HV

--EXISTS Example

select a, b from t1 tOne

where EXISTS

( select null from @holder h where h.hValue = tOne.a)

I'm still using your examples to learn how to fine-tune my eye for partition elimination.

# October 4, 2007 1:46 PM

sholliday said:

Man, what would be nice is something like:

set partitions profile on; --<this does not exist

And then it would just show you:

Table PartitionTouches

t1 1

Because it gets cumbersome/confusing sometimes.

I guess I can dream.

But thank you again for the post...I'd be lost/frustrated without it.

# October 4, 2007 1:50 PM

clientserverarch said:

Folks,

I have VLDB on SQL2000. Our primary table contains slightly less than 1B records, and I am anticipating 450M records per year.

We have implemented distributed view on 2000, and it works reasonably well. As we are migrating to SQL2005, I am not certain of the maturity of partitioning to migrate to that architecture, as well as having everything on a single server. My first reaction is to architect a hybrid solution, where I would use partitioning on the local servers, and then have a distributed view on top of all the partitions.

Any thoughts would be appreciated.

# January 15, 2008 5:14 PM

JungSun said:

Hi,

I'm Jungsun Kim, SQL Server MVP in Korea.

This is good article, I think.

(of course, too old. ^^)

I would like to introduce this article to my buddies.

so I just translated to Korean and posted in my blog,

"http://blog.naver.com/visualdb/50027176087"

Thank you.

# January 25, 2008 12:27 PM

LuborK said:

Answer to clientserverarch:

Your approach combining the partitioned tables and partitioned view will work. Before you implement the solution make sure you know WHY you want to partition. Table partitioning is usually introduced for manageability reasons, ability to load data w/out affecting the table access and ability to delete whole partition very fast. Sometimes users parition to enable index rebuild on a single partition or break down the backup/restore process. You should start with clear idea what you want to use it for and go from there. I would also first test it w/out the partitioned view to make sure that you are getting what you hoped for.

# January 25, 2008 5:17 PM

LuborK said:

Jungsun Kim, thanks a lot for translating to Korean. It is not late at all and we have many customers in Korea - here you can see some: http://www.microsoft.com/casestudies/search.aspx?Keywords=korea&ProTaxID=1273

# January 25, 2008 5:35 PM

clientserverarch said:

LuborK,

Thanks for your reply.

I am planning to distribute the data using both methods, separating the data out by server (perhaps 1-3 servers a year), and further separate out by month into different partitions.

I am concerned that MS is going to remove the distributed view functionality in future MSSQL releases, and then I will be back to consolidating data.

Thoughts?

# January 28, 2008 1:57 PM

LuborK said:

There is currently no plan to remove the support for distributed partitioned views in the future releases. That said I cannot ensure it will be supported forever, but similarly I cannot say it about SELECT statement :-).

# January 28, 2008 2:16 PM

kangana said:

Is there a plan to release a patch for this issue for SQL Server 2005 or it will be fixed in SQL Server 2008 only?

I hope Microsoft is planning to address this issue for SQL Server 2005 because partition elimination not working is the only reason we are not using partitioning in our environment.

Unfortunately we cannot use the workarounds suggested in this article because we do not have control over the structure of ad hoc queries generated in our environment.

# June 20, 2008 6:30 PM

LuborK said:

Kangana, it would be great if you could let us know what kind of queries you are looking for to eliminate partitions. Also please let us know what are the partitioning columns, clustered indexes. Then I can answer your question.

# June 23, 2008 3:02 PM

kangana said:

Thanks for the response Lubor.

We have a data warehouse environment. The query in its simplest form is:

SELECT ColA from FactTableA WHERE ColA in

(select ColB from DimensionTableB where ColC = somevalue )

FactTableA is partitioned on ColA but partition elimination does not work. ColA and ColB are data type int and the subquery returns list of integers. ColC is data type date.

FactTableA has a identity int column as its primary and clustered index key. There is a nonclustered index on ColA.

This query is generated through a third-party reporting tool Microstrategy.

User inputs value of 'somevalue' in a report, SQL query is generated by the reporting tool and then sent to the SQL Server thus resulting in no control over structure of query.

# June 25, 2008 3:13 PM

kangana said:

Sorry, query would be more like:

SELECT ColA, AmountA, AmountB, AmountC from FactTableA WHERE ColA in

(select ColB from DimensionTableB where ColC = somevalue )

# June 25, 2008 5:55 PM

LuborK said:

I'm not sure I understand how is your table decleared (with partitioning, uniquness, indexes, etc.). If ColA is the partitioned column and some other column is unique key, then there must be some other index on the unique column that is not partitioned on ColA.

If I ignore the above uncertainity and I only assume that the table is partitioned on ColA then we can use several different plans for this query - some of them visiting all partitions some of them not.

The following things would encourage using index lookup into the fact table thus avoiding searching all partitions:

1. create index on ColA and name the additional columns AmountA, AmountB, AmountC as INCLUDE columns in the index OR

2. create clustered index on ColA

But in both cases optimizer may still not choose the "seeks" if it assumes that there exist many rows qualified by the subquery (select ColB from DimensionTableB where ColC = somevalue )in the fact table.

# June 29, 2008 11:30 PM

kangana said:

The subquery will return multiple values for ColB and there are multiple rows in fact table for those values, which is the reason that causes partition elimination to fail.

I do have covering indexes on table using INCLUDE clause.

The fact table is declared with partitioning and ColA is the partitioned column. The fact table also has an auto generated int column ColIdentity (which is a standard practice in data warehousing). I did create a unique clustered index on combined (ColIdentity, ColA) for the fact table instead of creating a Primary Key for ColIdentity but that did not help.

I know that the subquery in its current form is the issue because the following queries do use partition elimination correctly:

Query 1

SELECT ColA, AmountA, AmountB, AmountC from FactTableA WHERE ColA = value1

Query 2

SELECT ColA, AmountA, AmountB, AmountC from FactTableA WHERE ColA in

(value1, value2, value3, value4)

I have done this as a test project and the partition elimination does not work. In fact, the queries on partitioned fact table were taking more time to execute than the queries on non-partitioned fact table. And our reason for doing this whole exercise was to improve query response times which didn't happen and so we did not do it in production.

As I said, unfortunately I cannot use the workarounds suggested in this article in my environment, we need a fix for this issue instead. I spent a lot of time figuring out why partition elimination was not working for my test until I came across this article and I had my answer!

Hence to my earlier question, are we getting a fix for the issue highlighted in this article for SQL Server 2005?

That is the only thing stopping us from using partitioning in our production environment and improving query response times! We have a 500GB production data warehouse on 64 bit SQL Server 2005 Enterprise Edition and any performance improvement is a big help because a large percentage of queries in our environment are done on the fly.

Thanks!

# June 30, 2008 2:17 PM

Anonymous comments are disabled

Microsoft SQL Server Development Customer Advisory Team

Recent Posts

Tags

News

Archives

Partition Elimination in SQL Server 2005

Comments

AssafF said:

DannyRa said:

LuborK said:

LuborK said:

Craig Freedman's WebLog said:

Sharepoint Experiences :: Brazilian SPS MVP said:

EricWelsh said:

LuborK said:

sholliday said:

LuborK said:

sholliday said:

sholliday said:

sholliday said:

clientserverarch said:

JungSun said:

LuborK said:

LuborK said:

clientserverarch said:

LuborK said:

kangana said:

LuborK said:

kangana said:

kangana said:

LuborK said:

kangana said: