SQL Server Partitioned View – Force Optimizer to Eliminate Irrelevant Tables

partitioningsql serversql server 2014

I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables.

1, 2, 3, 4

In testing this approach, I've discovered something that doesn't make a whole lot of sense to me. When I filter on "partitioning column" on the fact view, the optimizer only seeks on the relevant tables. Additionally, if I filter on that column on the dimension table, the optimizer eliminates unnecessary tables.

However, if I filter on some other aspect of the dimension the optimizer seeks on the PK/CI of each base table.

Here are the queries in question:

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where o.ObservationDateKey >= 20000101
    and o.ObservationDateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.DateKey >= 20000101
    and od.DateKey <= 20051231
group by od.[Year];

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where od.[Year] >= 2000 and od.[Year] < 2006
group by od.[Year];

fact filter on key

dim filter on key

dim filter on aspect

Here's a link to the SQL Sentry Plan Explorer session.

I'm working on actually partitioning the larger table to see if I get partition elimination to respond in a similar fashion.

I do get partition elimination for the (simple) query that filters on an aspect of the dimension.

In the meantime, here's a stats-only copy of the database:

https://gist.github.com/swasheck/9a22bf8a580995d3b2aa

The "old" cardinality estimator gets a less expensive plan, but that's because of the lower cardinality estimates on each of the (unnecessary) index seeks.

I'd like to know if there's a way to get the optimizer to use the key column when filtering by another aspect of the dimension so that it can eliminate seeks on irrelevant tables.

SQL Server Version: 

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Best Answer

Enable trace flag 4199.

I also had to issue:

UPDATE STATISTICS dbo.ObservationDates 
WITH ROWCOUNT = 73049;

to get the plans shown below. Statistics for this table were missing from the upload. The 73,049 figure came from the Table Cardinality information in the Plan Explorer attachment. I used SQL Server 2014 SP1 CU4 (build 12.0.4436) with two logical processors, maximum memory set to 2048 MB, and no trace flags aside from 4199.

You should then get an execution plan that features dynamic partition elimination:

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where 
    od.[Year] >= 2000 and od.[Year] < 2006
group by 
    od.[Year]
option (querytraceon 4199);

Plan fragment:

Plan fragment

This might look worse, but the Filters are all start-up filters. An example predicate is:

Filter properties

Per iteration of the loop, the start-up predicate is tested, and only if it returns true is the Clustered Index Seek below it executed. Hence, dynamic partition elimination.

This is perhaps not quite as efficient as static elimination, especially if the plan is parallel.

You may need to try hints like MAXDOP 1, FAST 1 or FORCESEEK on the view to get the same plan. Optimizer costing choices with partitioned views (like partitioned tables) can be tricky.

The point is you need a plan that features start-up filters to get dynamic partition elimination with partitioned views.

Queries with embedded USE PLAN hints: (via gist.github.com):

Related Question