Batch Hash Table Build

Introduction

The Batch Hash Table Build operator is used in SQL Server 2012 to build a “batch-mode bitmap”, which has the same function as the (row-mode) bitmap that is created by the Bitmap operator. On SQL Server 2014 and later, the Batch Hash Table Build operator is not used anymore, because batch-mode bitmaps can now be created by the Hash Match and Adaptive Join operators.

The generated bitmap is typically used in other operators to remove rows for which there is no match in the bitmap, and hence guaranteed no match in the original set of data processed by the Batch Hash Table Build operator. The use of Batch Hash Table Build operators is most common in execution plans for star join queries in large data warehouses.

Visual appearance in execution plans

Depending on the tool being used, a Batch Hash Table Build operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Batch Hash Table Build operator when running in row mode is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a batch of rows is returned, and resumes where it was upon next being called. It is also based on a “simple bitmap”, whereas in reality the storage structure of a batch-mode bitmap can be more complex, as described by Paul White.

When the Batch Hash Table Build operator runs in batch mode, it appears not to do any work at all. See the description below.

Compute Hash

The Batch Hash Table Build operator does not expose which column(s) from the input are hashed in order to determine which location in the bitmap to set. I assume that this data does exist in the internal representation of the execution plan, but was not mapped to any elements in the XML representation, and hence cannot be represented in the graphical execution plan either.

In practice, the column(s) to be hashed will always be the same as the column(s) in the Hash Keys Build property of the Hash Match operator to the immediate left (separated by only a Parallelism (Repartition Steams) operator) of the Batch Hash Table Build.

Row mode vs batch mode

The Batch Hash Table Build operator is only used in batch mode (sections of) execution plans, and the bitmap produced is what Paul White calls a “batch-mode bitmap”. Since SQL Server 2014, those same batch-mode bitmaps are created by Hash Match and Adaptive Join, and the Batch Hash Table Build operator is no longer used.

In row mode execution plans, “row-mode bitmaps” can be created by the Bitmap operator.

Not executing in batch mode

When a hash spill causes the execution plan to fall back to row mode, Batch Hash Table Build updates the bitmap for each row as shown in the flowchart above. This is the exact same functionality as the Bitmap operator, except the bitmap structure may be different, depending on whether the bitmap produces in case of row mode fallback is still a batch-mode bitmap, or a row-mode bitmap.

When no row mode fallback happens, the Batch Hash Table Build appears not to do any work at all. My assumption is that the logic to create the batch-mode bitmap in the Hash Match operator already existed in SQL Server 2012, but an explicit operator was needed to ensure the bitmap would still be created even in the case of a fallback to row mode. Since that can no longer happen as of SQL Server 2014, the Batch Hash Table Build operator was now no longer needed.

Because the Batch Hash Table Build doesn’t actually do anything if there is no row mode fallback, it will report zero for all the “Actual …” properties in an execution time plus run-time statistics. It will report correct numbers in the case of a row mode fallback.

Operator properties

The properties below are specific to the Batch Hash Table Build operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Bitmap operator are marked with a *.

Property name	Description
Actual Number of Batches *	This property only reports the a relevant value when the execution plan fell back from the estimated batch mode to row mode execution. If the execution plan was able to finish in batch mode, this property always reports 0.
Actual Number of Rows *	This property only reports the a relevant value when the execution plan fell back from the estimated batch mode to row mode execution. If the execution plan was able to finish in batch mode, this property always reports 0.
Actual Rebinds *	This property only reports the a relevant value when the execution plan fell back from the estimated batch mode to row mode execution. If the execution plan was able to finish in batch mode, this property always reports 0.
Actual Rewinds *	This property only reports the a relevant value when the execution plan fell back from the estimated batch mode to row mode execution. If the execution plan was able to finish in batch mode, this property always reports 0.
Defined Values *	This property lists the name of the created bitmap. This is especially important in execution plans that use multiple Batch Hash Table Build operators. There is no definition explicitly given for the listed column (not needed, it is always a bitmap). Unlike other operators that have a Defined Values property, the defined column is not added to the rows returned and hence not included in the Output List property. The bitmap is instead stored in a centrally accessible location in memory and stores information about all rows processed.
Number of Executions *	This property only reports the a relevant value when the execution plan fell back from the estimated batch mode to row mode execution. If the execution plan was able to finish in batch mode, this property always reports 0.

Implicit properties

This table below lists the behavior of the implicit properties for the Batch Hash Table Build operator.

Property name	Description
Batch Mode enabled	The Batch Hash Table Build operator is only used in batch mode (sections of) execution plans. However, it does support row mode execution, because in SQL Server 2012, a hash spill would force a batch mode execution plan to fall back into row mode. See the details in the main text.
Blocking	As far as known, the Batch Hash Table Build operator is non-blocking.
Memory requirement	As far as known, the Batch Hash Table Build operator does not have any special memory requirement. However, it does expose Memory Fractions Input and Memory Fractions Output properties in the execution plan, which is normally only done by operators that do have special memory requirements. I do not know why this is the case.
Order-preserving	As far as known, the Batch Hash Table Build operator is fully order-preserving.
Parallelism aware	The Batch Hash Table Build operator is only used in batch mode (sections of) execution plans, and only in SQL Server 2012. Since batch mode on SQL Server 2012 required parallelism, this implies that the Batch Hash Table Build operator can only run in parallel (sections of) execution plans. Also, the batch-mode bitmap that Batch Hash Table Build is a single bitmap that can be shared across threads, which implies that the Batch Hash Table Build would have to be parallelism aware. On the other hand, Batch Hash Table Build only actually works when there is a row mode fallback, and it is unknown whether it in that case still creates a batch-mode bitmap, or a row-mode bitmap. So it is also possible that Batch Hash Table Build is not parallelism aware.
Segment aware	As far as known, the Batch Hash Table Build operator is not segment aware.