HTMEMO belongs to the batch-mode hash table wait family (HTBUILD, HTDELETE, HTREINIT, HTREPARTITION). Since SQL Server 2014, parallel batch-mode hash joins and aggregations use one shared hash table across all threads; HTMEMO is the synchronisation before threads scan that shared table to output matches and non-matches, the pattern used by outer joins and some aggregations.
Typical home: columnstore workloads, though any batch-mode hash operator can generate it.
Is It a Problem?
The HT family measures skew between parallel threads, not the cost of the hash operation itself. If every thread finished its share at the same time, this wait would be near zero; when one thread lags, the others clock up HT time at each synchronisation point. HTMEMO climbing on your analytics workload means the output phase keeps waiting on imbalanced input work.
Moderate values on big columnstore queries are normal batch-mode behaviour.
Common Causes
- Skewed data on the join keys, especially with outer joins that must scan the full hash table to emit non-matching rows.
- High degrees of parallelism amplifying small imbalances into visible wait time.
- Statistics that no longer reflect the data distribution.
What To Do
- Look at per-thread actual row counts on the hash operators in the plan; skew is visible at a glance.
- Update statistics on the columns driving the hash distribution.
- Test the worst queries at lower MAXDOP; when skew cannot be fixed, less parallelism often nets faster wall-clock.
- Consider whether the query really needs the outer join semantics driving the full-table output scan.
How To See It
Rank it against everything else with Get-WaitStatistics. Read it as part of the HT* family; the fix is shared across all of them.
Part of the SQL Server Wait Types Library.
Related deep dive: CXPACKET and CXCONSUMER Wait Types.
Leave a Reply