Hash Join Performance

Paul White wrote something.  That’s good enough to get tagged here:

It might be tempting to ask why the optimizer does not routinely add null-rejecting filters prior to equality joins. One can only suppose that this would not be beneficial in enough common cases. Most joins are not expected to encounter many null = null rejections, and adding predicates routinely could quickly become counter-productive, particularly if many join columns are present. For most joins, rejecting nulls inside the join operator is probably a better option (from a cost model perspective) than introducing an explicit Filter.

It does seem that there is an effort to prevent the very worst cases from manifesting through the post-optimization rewrite designed to reject null join rows before they reach the build input of a hash join. It seems that an unfortunate interaction exists between the effect of optimized bitmap filters and the application of this rewrite. It is also unfortunate that when this performance problem does occur, it is very difficult to diagnose from the execution plan alone.

I’m only on reading #2 of the post, so I’ve got a few more things yet to learn from it.

Related Posts

Performance Concern: Anti-Join And Top

Paul White explains a scenario in which an innocent-looking execution plan can hide something sinister: Not every execution plan containing an apply anti join with a Top (1) operator on its inner side will be problematic. Nevertheless, it is a pattern to recognise and one which almost always requires further investigation. The four main elements […]

Read More

Character Columns And MAX Vs TOP+ORDER Differences

Kendra Little digs into a tricky performance problem: Most of the time in SQL Server, the MAX() function and a TOP(1) ORDER BY DESC will behave very similarly. If you give them a rowstore index leading on the column in question, they’re generally smart enough to go to the correct end of the index, and […]

Read More


November 2015
« Jan Dec »