4

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

7

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.

  • Thanks for your answer. Regarding index seek: I thought originally of an index range seek, which would be executed 3 times (once for each entry in the #termin table) and still using a hash join operator, but I guess it is not possible for the same reasons as the partition elimination (the optimizer "forgets" that there are only 3 different values for termin possible) – Thomas Franz Jun 19 '17 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.