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
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
- With
OPTION (MAXDOP 1)
the execution plan reads no rows from#BigTable
. TheActualExecutions
is0
for all operators on the inside of the hash join. - 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 haveActualExecutions
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?