Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?
Martin Smith (imported from SE)
## 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? : https://i.stack.imgur.com/XC3O9.png