HT Waits – Explained and Animated

Remember when you started learning wait stats, and were told that CXPACKET just means parallelism, nothing to see here, move on? HTBUILD is the new CXPACKET. Microsoft documentation on the HT waits is a little, uh, thin. Have a problem? Increase the cost threshold for parallelism (which you still can’t do in Azure SQL DB by the way – it’s stuck at 5).

If you’re anything like me, the CXPACKET explanation wasn’t sufficient, and the current situation with HT waits certainly isn’t either. Googling for them didn’t turn up much, so I put on my safety goggles and fired up extended events. And lots of demo queries. And the debugger. I didn’t even bluescreen my system this time, probably due to the goggles.

There are three main HT waits that I focused on: HTREPARTITION, HTBUILD, and HTDELETE. To get these, you need a query plan where three things combine: a (1) parallel (2) batch mode (3) hash operation.

I’m working on an amateur explanation of the batch mode hash join, which I’ll later link to. The short version is this: it starts with a Repartition phase, which is where it consumes batches of rows. I assume there’s some repartitioning going on, but I don’t know what that actually means here. There’s a synchronization gate at the end of this phase, and any threads that finish their work before the others will wait with HTREPARTITION.

After this is the Build phase, which seems to actually build the hash table. Like earlier, there’s a gate at the end, and threads that finish first wait with HTBUILD.

Afterwards is the Probe phase. This is where the hash join finds matching rows and outputs them. There are couple non-HT waits here, and skew normally shows up with our old friend CXPACKET.

Next comes Cleanup phase…except one of the weird things about batch mode hash operations is that they can share memory. So “next” might not be next, but after whatever hash operations are chained together. Cool huh? Same deal here – threads that are done wait with HTDELETE.

Blah blah blah, who learns by reading? LOOK AT THE PRETTY PICTURE:

Some notes: all of the hash aggregates I tested lacked a Repartition phase. This means that HTREPARTITION only shows up for joins. I’m not certain of this however, and would love a repro of a hash agg with this wait. Also, a thread can repeat an HT wait within a phase. There seem to be some extra synchronization activities at the end, and workers blink on and off with these before moving on.

I admit it; ultimately, HT* waits do look a lot like CXPACKET. They show up with parallelism and skew, and there’s not much you can do to easily affect them (ignoring silly MAXDOP 1 suggestions). I learned enough about these that I think I could track down problems if necessary, but so far it hasn’t been necessary. Thankfully I’m a nerd, and knowledge is its own reward. And knowledge with a gif is even better.