HTDELETE is the best known of the batch-mode hash table waits. Since SQL Server 2014, parallel batch-mode hash joins and aggregations share a single hash table across threads (2012 built one per thread), trading memory savings for synchronisation. HTDELETE is threads waiting on that synchronisation at the end of the hash join or aggregation, when the shared table is being torn down.
It arrived with a reputation because it appeared suddenly on systems upgraded to 2014 and shot up wait rankings on columnstore workloads.
Is It a Problem?
Like the whole HT family, this wait measures thread skew, not operation cost. Balanced parallel work produces near-zero HT time; skewed work makes the finished threads wait at the teardown for the straggler. So a big HTDELETE number on an analytics server says “your batch-mode work is unevenly distributed”, not “hash joins are broken”.
One specific case worth knowing: heavy HTDELETE during updates to clustered columnstore indexes can come from rowgroups carrying a lot of deleted rows, and rebuilding the index fixes it.
Common Causes
- Data skew on join or grouping keys across parallel threads.
- High MAXDOP magnifying the cost of any imbalance.
- Clustered columnstore indexes with many deleted rows per rowgroup being updated.
- Stale statistics distributing work badly.
What To Do
- Check per-thread row counts in the actual plan of the biggest batch-mode queries; confirm skew before changing anything.
- For the columnstore update case, check rowgroup health in
sys.dm_db_column_store_row_group_physical_stats(deleted_rows) and rebuild affected indexes. - Update statistics on the hash keys, and reconsider MAXDOP for chronically skewed queries.
- Accept a baseline: batch mode with real-world data always shows some HT* time.
How To See It
Rank it against everything else with Get-WaitStatistics, and read HTDELETE together with HTBUILD, HTMEMO, and HTREPARTITION as one skew signal.
Part of the SQL Server Wait Types Library.
Related deep dive: CXPACKET and CXCONSUMER Wait Types.
Leave a Reply