Sql-server – why no partition elimination

partitioningsql serversql server 2014

I have a temp table #termin which contains 3 rows

When I execute the following query

SELECT t.termin, ttw.tourid, twt.va_nummer_int
  FROM #term AS t
 INNER JOIN plinfo.t_touren_werbeflaechentermine AS ttw
     ON ttw.termin = t.termin
  INNER JOIN wtv.t_werbeflaechentermine AS twt 
     ON twt.jahr   = t.jahr
    AND twt.termin = t.termin
    AND twt.ID_Wt  = ttw.id_wt
    GROUP BY t.termin, ttw.tourid, twt.va_nummer_int

I'll get the following execution plan:
enter image description here

Each table is joined using a matching index. Both tables are partioned by ps_termin(termin).

For the first table (t_touren_werbeflaechentermine) it does an partition elimination and read only a subset of row, while for the second table (t_werbeflaechentermine) it scans the whole index (jahr, termin, id_wt include (va_nummer_int)).

So my question: why does it an index scan (and no seek) and why does it not eliminate the partitions for the second table.

PS: when using WITH (FORCESEEK) on the second table, it switchs both tables in the execution plan and does the full index scan on the first one…

PPS: Execution plan can be found here

Best Answer

Why an index scan?

It's likely that SQL Server estimated a lower cost for scanning the data (only 1.7MM rows, so a relatively small table) as compared to a loop-seek approach.

Scanning the t_werbeflaechentermine table will process all 1,708,658 rows, and your plan shows that this performs 7,347 logical reads.

A loop-seek is estimated to perform ~70K seeks (the cardinality estimate for the outer side of the loop). If we assume a seek is a binary search, we might therefore estimate that this will have a complexity of 70109.4 * LOG(1708658,2), or 1,451,575.

This is slightly lower than the 1,708,658 rows processed by the scan, but the number of logical reads will be much higher since each of the (estimated) ~70K seeks will perform several logical reads, yielding many more logical reads then the scan that was chosen. This might be the reason that a loop-seek plan yields a higher estimated cost and was not selected.

Viewing the plan without scans

If you'd like to compare this plan to the execution plan that uses a loop-seek is used for both joins, you can try adding the OPTION (LOOP JOIN) query hint to your query so that both joins use a loop join. It would be informative to post this actual execution plan for comparison.

Why no partition elimination?

I believe that partition elimination does not occur for t_werbeflaechentermine because SQL Server does not have a hash join algorithm that performs partition elimination based upon the observed partitions on the build side of a hash join. This would be a nice optimization in some cases, but to my knowledge is not available in the current optimizer: Partition elimination is available for the inner side of a loop join, but not for the probe side of a hash join (unless the query contains an explicit predicate on the partition column).

For further reading, SQL Server does have the concept of a collocated join, in which the hash join is applied independently for each partition of two tables that are partitioned the same way. However, this optimization is only available 2-way joins, so your query does not qualify. Paul White describes this and other partitioned table join considerations in much more detail in Improving Partitioned Table Join Performance.

Related Question