HTBUILD is one of the batch-mode hash table waits (with HTDELETE, HTMEMO, HTREINIT, and HTREPARTITION). From SQL Server 2014 onward, parallel batch-mode hash joins and aggregations share one hash table between all threads instead of building one per thread, which saves a lot of memory but means the threads must synchronise. HTBUILD specifically is threads coordinating while the hash table is being built on the input side of the join or aggregation.
You will mostly see it on workloads with columnstore indexes, though any hash operator running in batch mode can produce it.
Is It a Problem?
Here is the counter-intuitive part: these waits do not measure how expensive the operation is, they measure how much skew there was between the parallel threads. With perfectly balanced work, HT* waits are near zero; the more unevenly the rows are distributed across threads, and the higher the degree of parallelism, the more the fast threads wait for the slow one. So high HTBUILD is a data distribution signal, not a hash-table-is-slow signal.
Steady moderate amounts on an analytics workload are the cost of doing business with batch mode.
Common Causes
- Skewed data distribution on the hash keys, leaving one thread with most of the build work.
- Very high MAXDOP amplifying the synchronisation cost of any imbalance.
- Stale statistics leading the optimiser to distribute work poorly.
- Large columnstore queries where hash aggregations dominate the plan.
What To Do
- Confirm the skew: the actual execution plan shows per-thread row counts on the hash operators; a 90/10 split across threads is your answer.
- Update statistics on the join and grouping columns so the distribution is understood.
- Experiment with lower MAXDOP for the affected queries; less parallelism means less synchronisation overhead when skew is unavoidable.
- If one hot key value causes the skew, consider whether the query can pre-aggregate or split that value’s processing.
How To See It
Rank it against everything else with Get-WaitStatistics. Read all HT* waits as one family; they rise and fall together with batch-mode skew.
Part of the SQL Server Wait Types Library.
Related deep dive: CXPACKET and CXCONSUMER Wait Types.
Leave a Reply