Say I have a query like this:

SET @ContainerId = 5

FROM    Container.Container_Partitioned 
WHERE   containerId = @ContainerId

When I run this, I get a query plan with Actual Partition Count equal to 1. (Good)

But if I run this:

SET @ContainerId1 = 5
SET @ContainerId2 = 30000000

FROM    Container.Container_Partitioned         
WHERE   containerId = @ContainerId1 OR ContainerId = @ContainerId2

I get an Actual Partition Count equal to all my partitions. (Bad)

I have two questions:

  1. Why can the optimizer figure it out with one parameter, but not with two?
    I guess it is the "OR" operator. But why?
  2. How can I get it to correctly see that only two partitions are need without using OPTION(RECOMPILE) or something similar?
    I can recompile each time if I have to, but I would rather not for obvious reasons.
  • 1
    Is ContainerID the partitioning key? Did you try UNION ALL instead of OR? – Aaron Bertrand Jun 25 '15 at 0:32
  • @AaronBertrand - Yes it is. And a union does work. Is my only option to replace all my "or"s and "in"s with repeat Union queries ? And if so, why? – Vaccano Jun 25 '15 at 0:37
  • 1
    Perhaps TF4199 would help? – Max Vernon Jun 25 '15 at 3:52

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods. These both use a JOIN instead, one with a row constructor and the other a table variable. The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing the query itself.

FROM    Container.Container_Partitioned
JOIN ( VALUES ( @ContainerId1), ( @ContainerId2) ) AS containers ( ContainerId ) 
    ON containers.ContainerId = Container_Partitioned.ContainerId;

DECLARE @Containers TABLE(ContainerId int PRIMARY KEY);
INSERT INTO @Containers VALUES(5);
INSERT INTO @Containers VALUES(30000000);

FROM    Container.Container_Partitioned
JOIN @Containers AS containers ON Containers.ContainerId = Container_Partitioned.ContainerId;

As to why, SQL Server generates a plan that can be reused regardless of the values that may be specified. With an index on ContainerId, I get a seek with a partition range as the first seek predicate and the supplied container values as the second. Remember that the partition number is treated much like the first column of an index an index in SQL 2008 and later. The partition range seek must allow for all partitions in order to be able to return any possible container id values with that plan.

Note that even though the unneeded partitions are touched, the number of logical reads for each is just the number of index levels since no rows will be found for those. Not ideal, especially if you have a lot of partitions, but not too expensive since those pages will likely remain memory resident if the query is executed often enough.

Interestingly, it seems SQL Server can eliminate partitions better in this case when the partitioning column is not the first column of an index. Without an index on ContainerId, the plan I get scans with a nested loop operator using a RangePartitionNew function to only access the partition with the specified container ids. One would think SQL Server could generate a similar plan for the index seek. There seems to be room for improvement but keep in mind that partitioning adds another layer of optimizer on top of the already complex job to optimizer has to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.