Partitioned Tables And Parameters

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