Sql-server – Does the hash join operator always pull from the build side

sql serversql server 2014

Watching queries execute with Live Query Statistics I noticed that it seems SQL Server is lazily constructing a hash table from the build input of a hash join.

This is a meaningful difference in the case of 0 probe rows. It potentially saves the entire build side tree.

I always thought a hash ran like this:

  1. Create build-side hash table.
  2. Match all probe rows against it.

But is it the following instead?

  1. Pull first probe row.
  2. Complete the operation if no row available (short-circuit).
  3. Match all probe rows against it.

I'm not sure how to conclusively test which way it is. I'm not sure the Live Query Statistics output can be trusted in this way. Does anyone know how this works?

Best Answer

If you post the actual query plan for your specific query, we might be able to comment on that more directly.

But in general, I believe (and have always observed) that the hash join operator does always runs as you expected:

  1. Create build-side hash table.
  2. Match all probe rows against it.

This generally makes sense given that SQL Server will place the smaller (estimated) rowset on the build side of the join. The probe side is expected to be larger than the build side (and therefore to contain at least some rows).

In addition, SQL Server has a potential bitmap optimization that it can apply to the probe side of a hash join in some cases, but this optimization requires that the build side be processed first.

An example

For a simple query where we force a large build side of the hash join and an empty probe side, the actual execution plan shows that all rows are processed on the build side of the join.

-- Fully processes the build side of the hash join
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number

enter image description here

Can the build side ever be skipped?

There is at least one example where the build side of the hash join can be skipped: In some cases, the entire join can be eliminated. For example, here SQL Server is able to prove prior to execution that the probe side will have 0 rows. The final query plan is therefore a constant scan and there is no hash join.

-- No hash join at all; the query optimizer realizes no rows are guaranteed
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE 0=1
) p
    ON p.number = b.number

What about batch mode?

Based on a quick test, it seems that the batch mode hash join operator will also fully process the build side of the hash join.

-- The batch mode hash join also fully processes the build side
-- even if there are 0 rows on the probe side
SELECT number
INTO #spt_values
FROM master..spt_values
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_#spt_values ON #spt_values
GO
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM #spt_values v1
    CROSS JOIN #spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM #spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number
GO
Related Question