Performance:Hash join elimination

donb 2017-04-03 04:19:14

How to avoid a hash match join operator in an execution plan?
Aaron Bertrand 2017-04-11 21:16:44
Hash is the algorithm SQL Server will use when it has two large inputs and no obvious way to join in a sorted manner (usually means one or both sides of the join are on unindexed columns, or columns that are indexed in the wrong direction, not left enough in the key, etc).

You can try to force a loop or merge join with a hint, but this should be a last resort (and most often you'll find that this ends up with a less optimal plan). In general I would say the real solution is to make sure you have indexes that support your join operation.

donb 2017-04-11 23:34:53
Thank you so much Aaron! Yes, there was indeed a missing index on the predicate columns on one side of the join.