Inefficient queries on partitioned tables

Question

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.

Answer 1

Right okay, after managing to get a little more time to look at this I have found the issue. Obvious really but it always is in hindsight.

So, partition elimination occurs when you specify a literal as the filter on the partitioning column. Eg. WHERE partition_column = 20150812. Cool.

However, what if you might need to filter on more than one date. Maybe you want to find the difference between two arbitrary days worth of data or sum them up or something. And let's say that the set of dates will be provided at runtime via some input. At this point you cannot hard code the values in the WHERE clause without using dynamic SQL.

Now if you put the values into a temporary table and perform an inner join to the partitioned table on the partitioning column, SQL Server no longer has direct visibility of the partitions on which to filter and seemingly falls back to a table scan. On huge tables this is a big problem. SQL Server seemingly has no concept of late bound or lazy partition elimination when using the partitioning column in a join to filter on for various reasons I'm sure.

So given this scenario I can't say I have found any article that describes the best practices around a good PK for partitioned tables. The process we have gone through thus far is:

1. Use the $PARTITION function in the join to force SQL Server to use the partition that would contain that partitioning value. However, using this on its own is incorrect as it will return data from any partition that would contain the value even if the value does not exist.
2. Use the above function along with the partitioning column in the join to force late partition elimination and guarantee that you get the right data. However, this has a nasty side effect. Doing this causes the optimizer to take the number of rows in the partition and divide it by the number of partitions, producing an incorrect row count. This unfortunately means, amongst other things, that a parallel execution plan often becomes serial and has been seen to dramatically increase execution time over a parallel plan on the partitions.
3. The most recent attempt to "fix" this is to place the partitioning column as the leading column in the PK and this does seem to work. However, we are yet to asses the negative impact of a low cardinality leading column. I suspect we may need to manually create stats on the next column (previously the leading column) or wait for AUTO STATS to do it for us but sure if that will be enough.

Answer 2

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.