11

Setup

DROP TABLE IF EXISTS #EmptyTable, #BigTable

CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);

INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM   sys.all_objects o1,
       sys.all_objects o2,
       sys.all_objects o3;

Query

WITH agg
     AS (SELECT DISTINCT a
         FROM   #BigTable)
SELECT *
FROM   #EmptyTable E
       INNER HASH JOIN agg B
                    ON B.A = E.A;

Execution Plan

enter image description here

Problem

This is a simplified repro for a phenomenon I hadn't noticed before today. My expectation for an inner hash join would be that if the build input is empty the probe side should not be executed as the join can return no rows. The above example contradicts that and reads the 10 million rows from the table. This adds 2.196 seconds to the execution time of the query (99.9%).

Additional Observations

  1. With OPTION (MAXDOP 1) the execution plan reads no rows from #BigTable. The ActualExecutions is 0 for all operators on the inside of the hash join.
  2. For the query SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A- I get a parallel plan, the scan operator on the inside of the hash join does have ActualExecutions of DOP but still no rows are read. This plan has no repartition streams operator (or aggregate)

Question

What's going on here? Why does the original plan exhibit the problem and the other cases don't?

2 Answers 2

10

Not running the probe side of the join when the build is empty is an optimization. It is not available for parallel row mode hash join when the probe side has a child branch i.e. when there is an exchange operator.

There was a similar report many years ago by Adam Machanic on the now-defunct Connect feedback site. The scenario was a start-up Filter on the probe side, which ran its child operators unexpectedly. The answer from Microsoft was that the engine requires a guarantee that certain structures are initialized, and the only sane way to enforce that was to ensure the probe side operators are opened.

My own recollection of the details is that not initializing a sub-tree led to hard-to-fix parallel timing bugs. Ensuring the child branch started up was a work around for those problems.

Batch mode hash join does not have this side effect because the way threads are managed is different.

In your particular case, the effect is more pronounced because the hash aggregate is blocking; it consumes its entire input during the iterator's Open() call. When there are only streaming operators on the probe side, the performance impact will often be more limited, depending on how much work is required to return the first row to the probe side of the hash join.

0
1

Not an answer but if hash join is not forced that query wont get hash join as a plan. A workaround is to set a bit variable to 1 if rows exist in the table and 0 if not and instead of #Emptytable use (Select * from #Emptytable where @bit =1)

And add an option recompile to the end , no execution will happen.

I think that condition should never happen if forcing is not used and if forcing needed a workaround exists.

2
  • In my actual production use case the build input is a more complicated sub tree but, yes, materialising this into a #temp table and assigning a variable based on @@ROWCOUNT should work - in my case I should be able to optimise the probe subtree so that it is much cheaper to evaluate and the query won't go parallel in the first place. Dec 6, 2019 at 8:33
  • My experience with hash joins that they work well with tables (bitmap indexes etc.... ), Our workload consists mostly of large tables (some over 5tb), for the very large tables the joins are done small chunks at a time (clustered index with multi columns or partitions). I can see things can go sour with other probe inputs.
    – Gokhan
    Dec 6, 2019 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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