What do the HT* wait types mean? (HTREPARTITION, HTDELETE, ...)

Question

A big query is running right now. The result of a batch mode hash join that spills to disk is streamed into a temp table using select into. The query is showing wait types HTDELETE and HTREPARTITION occasionally. The query is not using columnstore indexes.

I'm quite sure that these wait types are normal for batch mode hash joins that spill to disk. I'm trying to understand what these wait types mean out of curiosity. Maybe this insight can help optimize the query or find problems. So what do these wait types mean and under what circumstances are they expected?

No columnstore indexes. I'm using the left join ZeroRowCsTable on 0=1 trick to enable batch mode for row mode tables.

Answer 1

_{Community Wiki answer generated from a comment on the question by ypercube}

---

From an MSDN Forums thread:

HTDELETE

SQL Server 2014 now uses one shared hash table instead of per-thread copy. This provides the benefit of significantly lowering the amount of memory required to persist the hash table but, as you can imagine, the multiple threads depending on that single copy of the hash table must synchronize with each other before, for example, deallocating the hash table. To do so, those threads wait on the HTDELETE (Hash Table DELETE) wait type.

(This only applies to hash operators that execute in batch mode)

Answer 2

Could you just try updating your statistics and running this again? Perhaps it will work? Pls let us know if it does.

ref : a Connect item on Microsoft and it's comment: