Inefficient queries on partitioned tables Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the partitioned table directly on the partitioning column. (Not using $PARTITION, just purely column values.) The query includes joins to other dimension tables in a typical star-schema type way.

Furthermore, there are no non-clustered indexes on the table. The leading column in the clustered PK (ignoring the partitioning key id which is always leading) is a column that joins to one of the star-schema dimension tables and will include a filter. However, the filter on the dimension table would not be selective enough to cause an index seek, if there was an index, if the partitions were just a single table.

So, given the above, why is it that queries on this partitioned table do not always use partition elimination? When would it ever be more efficient to scan a partitioned table than just the partitions required?

At worst a partitioned table with one partition would be the same, ignoring the cost to determine the partition elimination etc.
It depends on the query, so if you have a specific problem you'll need to give us details for the query (and possibly table structure) in order to get specific help.

Generally speaking though:


If the filter is not sargable then it is no help to the query planner in the same way as references to indexes where the clause is not sargable - for instance if a function or sub-squery that can not be simplified down to a single value for all possible rows is involved.
If there are other filters on columns covered by non-partitioned (or unaligned) indexes the query planner may consider searching that way to be more efficient than using the partitioning rule.