HTREPARTITION completes the batch-mode hash table wait family. When a parallel batch-mode hash join or aggregation discovers its shared hash table will not fit as built, it repartitions the table’s input to process it in smaller pieces, and every thread must synchronise around that repartitioning. This wait is the queue at that synchronisation point.
As with all HT* waits, columnstore workloads are the usual context, but any batch-mode hash operator qualifies.
Is It a Problem?
Two things speak through this wait. The family-wide message: parallel threads were skewed, and the synchronisation points collected the imbalance as wait time. The HTREPARTITION-specific message: hash tables are being repartitioned at all, which means the hash operation outgrew what it expected, usually a memory grant based on a bad row estimate.
A workload that shows HTBUILD/HTDELETE but little HTREPARTITION has plain skew. One where HTREPARTITION features means the size estimates themselves are off.
Common Causes
- Underestimated hash input sizes (stale statistics, complex predicates) forcing repartitioning mid-operation.
- Memory grants too small for the actual build input.
- Data skew stretching each repartitioning synchronisation.
- Very large joins on high-cardinality keys in columnstore queries.
What To Do
- Find the plans doing it: big batch-mode queries with hash spill warnings or large estimate-vs-actual gaps on the hash operators.
- Update statistics on the hash key columns; better estimates mean right-sized hash tables that never repartition.
- On SQL Server 2017+ let memory grant feedback correct repeat offenders, and check it is not being defeated by wildly varying parameter values.
- Address thread skew as with the rest of the family (per-thread row counts, MAXDOP experiments).
How To See It
Rank it against everything else with Get-WaitStatistics. HTREPARTITION rising relative to its siblings is your cue to look at estimates and grants, not just skew.
Part of the SQL Server Wait Types Library.
Related deep dive: CXPACKET and CXCONSUMER Wait Types.
Leave a Reply