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.
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
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.