Performance:Hash join elimination

How to avoid a hash match join operator in an execution plan?

avatar image By donb 1 asked Apr 03, 2017 at 04:19 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 11, 2017 at 09:16 PM
more ▼
(comments are locked)
avatar image donb Apr 11, 2017 at 11:34 PM

Thank you so much Aaron! Yes, there was indeed a missing index on the predicate columns on one side of the join.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 03, 2017 at 04:19 AM

Seen: 117 times

Last Updated: Apr 11, 2017 at 11:34 PM