Partitioned Tables and Parameters

SQL Server Database Engine

SQLDataMonkey


Hello all,

I was wondering if anyone else ran into this and if how you got around it.

In a nut shell the SQL optimizer it NOT pruning the additional partitions from the execution plan as would be expected when applying a constraint directly against the partitioned table¡¯s partition key, Instead its scanning every partition that you have set up in you partition function range.. Yet when you apply the actual value against the table the plan return as expected.

Hmm.... strange......ghost...ooooooo

I have created a simple test to reproduce:

Code Snippet

CREATE PARTITION FUNCTION [PTFunction](int) AS RANGE LEFT FOR VALUES (1,2,3)

GO

CREATE PARTITION SCHEME [PTDataScheme] AS PARTITION [PTFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE tblPartitionTest(

ID int identity(1,1) ,

PartitionKey int,

Sales money)

ON PTDataScheme(PartitionKey)

GO

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(1,10.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(1,20.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(1,30.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(1,40.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(1,50.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(2,10.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(2,20.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(2,30.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(2,40.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(2,50.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(3,10.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(3,20.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(3,30.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(3,40.00);

INSERT INTO tblPartitionTest(PartitionKey,Sales) VALUES(3,50.00);

set showplan_text on;

-- query using the set value as a constraint

select * from tblpartitiontest

where partitionkey = 2

--show plan text on result:

|--Table Scan(OBJECT:([tblPartitionTest]), WHERE:([tblPartitionTest].[PartitionKey]=(2)) PARTITION ID:((2)))

-- query using the parameter as a constraint

declare @param_partitionkey int

set @param_partitionkey = 2

select * from tblpartitiontest

where partitionkey = @param_partitionkey

--show plan text on result:

|--Table Scan(OBJECT:([tblPartitionTest]), WHERE:([tblPartitionTest].[PartitionKey]=[@param_partitionkey]) PARTITION ID:(RangePartitionNew([@param_partitionkey],(0),(1),(2),(3))))

BTW I have reproduce this in SP 2 as well.

Any thoughts

Thanks

Eric

Top

Re: Partitioned Tables and Parameters

Jag Sandhu


Hi Eric,

I guess you need to try creating an index.

create clustered index idx_part on tblPartitionTest(PartitionKey)

go

regards

Jag






Top

Re: Partitioned Tables and Parameters

SQLDataMonkey

Hi Jag, thanks for the reply, though I don¡¯t see how or why a clustered index is going to solve this issue, seeing its an issue with the optimizer not performing the correct partition selection, and also because the clustered index is actually several clustered indexes, one per partition. Granted the clustered index will order the pages by the key, which I assume the partition function has are ready taken care of that.

Thanks

Eric



Top

Re: Partitioned Tables and Parameters

Dhericean

I think the problem is that when the value you are selecting by is a constant then the query planner can optimise based upon that value. However when it is a variable it cannot see (or ignores) the value of the variable when it is generating the query plan and so must generate a more general plan which will work for any value of the variable.

This is how it works by design I believe and allows it to reuse the query plan for other invocations of the same query (changing the value of the variable does not change the query).


Top

Re: Partitioned Tables and Parameters

SQLDataMonkey

Hi Dhericean, I agree,

Hmm... Interesting, that would make since though you would think that even after the query was executed and a actual execution would represent the actual parameter that was used.

Other then having to look and the I/O stats i wonder how you can tell if the optimizer is true pruning verses scanning all partitions.

Thanks

Eric


Top

Re: Partitioned Tables and Parameters

Dhericean

The parameter value is not part of the plan but rather one of the inputs to the execution itself. So the actual form of the plan is not dependant upon the value of the parameter.

There is also an issue to watch for of confusion between the argument to your partition function (the variable in this case) and the identity of the partition for that value. This would be less confusing if the input to the partition function were alphabetic rather than an integer. SQL Server does not short circuit this and I suspect that the RangePartitionNew in the plan is actually a way of embedding the logic for determining the partition ID into the plan (so that it can determine at run time which partition to scan).

If I take your example and change the partition key to a char(1) and use 'B', 'C', 'D' as the left limits then the final plan is:

|--Table Scan(OBJECTSad[AJR_TEST].[dbo].[tblPartitionTest]), WHERESad[AJR_TEST].[dbo].[tblPartitionTest].[PartitionKey]=[@param_partitionkey]) PARTITION IDSadRangePartitionNew([@param_partitionkey],(0),'B','C','D')))

The change in the arguments of RangePartitionNew indicates to me that the plan includes the information for it to determine the correct PartitionID from the value of the parameter so it is not scanning all the partitions. It is only placing all the information about the partitions into the plan so that it can determine the correction Partition ID at run time regardless of the value of the variable (allowing for plan reuse).


Top

Re: Partitioned Tables and Parameters

Jag Sandhu

Hi Eric,

Did you try running the select after creating the clustered index. try and check the execution plan.

With regards to parameters within the plan, you might want check set the PARAMETERIZATION option to FORCED using ALTER database. Try the index first though.

regards

Jag





Top

Re: Partitioned Tables and Parameters

Chris Mitchell

Have a look at this blog post from Lubor Kollar on the SQL Customer Advisory Team. I think it will answer all your questions.

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


Top

Re: Partitioned Tables and Parameters

Michael Hotek

This is normal for anything in the optimizer. Look up parameter sniffing. Basically, SQL Server takes the inputs that you send in when the optimizer goes to determine the plan. If your code manipulates an input parameter in any way before it is used, then the optimizer will not know what the resultant value could possibly be and therefore it will go for a more general plan. You can see this effect very simply by doing something like the following:

create procedure myproc @input1

...

<some query that uses @input1>

go

create procedure myproc @input1

...

declare @var

set @var = @input1

<some query that uses @var>

go





Top

Re: Partitioned Tables and Parameters

SQLDataMonkey

Hi Dhericean,

Thanks for the explanation, that makes since ( I didn't think to think the optimizer was attempting to build a generic plan that can except a parameter verses rebuilding a new plan for every potential partition, ( pretty clever...).

Thanks

Eric


Top

Re: Partitioned Tables and Parameters

SQLDataMonkey

Hey Chris,

LOL !!

Funny i just pulled that artical up this morning ( i agree and thanks for the link)

Thanks




Top