I am having a temp table with a clustered index scan operator which is being passed to a hash match join operator
But before being passed to hash match join operator, there is a Parallelism (repartition streams) operator which is consuming 17% cost in the execution plan.
How can I reduce this processing cost in the plan ?
By PARAG 1 asked Jun 01, 2016 at 03:21 PM
The 17% cost is an estimate, based on the estimated number of rows and their distribution of values, plugged into the optimizer's cost model. This calculation is used to choose between plan alternatives, but the numbers are actually quite unlikely to reflect the actual time spent on that operation on your particular hardware. A post-execution (actual) plan was not provided, so it is hard to say what the impact of that particular operator is.
Note that there is a bitmap filter applied to the Table Scan. This (if effective) may well result in many fewer rows encountering the exchange (Repartition Streams Parallelism operator) than expected. This is particularly true since the Bitmap is a type applied heuristically after optimization is completed - the optimizer knows nothing about it.
In general, exchanges are required on both inputs to a hash or merge join, to distribute rows that will join to the same thread. The exception, for hash join only, is where the complete hash table is copied to all threads (build side partitioning mode Broadcast). Since this consumes more memory, a broadcast hash join is only chosen if the number of build side rows is expected to be small (a few hundred rows or so).
Your other option to avoid exchanges is to persuade the optimizer to choose a nested loops join instead of hash or merge. This would require an index on
A suitable index would be keyed on
If this represents most of the columns of the table, you might choose to make
By SQLkiwi ♦ 6.6k answered Jun 02, 2016 at 05:49 AM