I am just starting to delve into query plans and recently learned that "hash matches" are bad. I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash match.

My query, which includes two tables, uses parallelism. It uses clustered index seeks on the two tables then it goes to the step Parallelism (Repartition Streams) with partitioning type of hash after both of these seeks. For one table, a bitmap is created and then these two are joined (inner join) by hash match.

My understanding is if the data to be joined is ordered, a merge join would be used - and this is preferable. The clustered indexed seek is ordered.

So... I guess I'm just looking for some information on this situation. Do hash matches always follow repartitions of streams? Does that repartition ignore order? Or should I not be bothered by this "evil" hash match?

Thank you!!

2 Answers 2


Hash matches are not necessarily bad @ all. Please see Grant Fritchey's awesome (and free!) book: SQL Server Execution Plans

  • 3
    +1 - None of the join types are better than the other, they all have their uses and proper use cases. Hash match is the most versatile of the three.
    – JNK
    Mar 6, 2012 at 16:15
  • 1
    This is a fantastic resource! You probably just saved me from posting another dozen questions! :)
    – JHFB
    Mar 6, 2012 at 16:17
  • Agreed, I can't believe it's free. Thank you Redgate :) Mar 6, 2012 at 16:19

What you see in that Execution plan is nothing unusual. It's quite common to have these steps in the execution plan when you're dealing with small-large tables. Depending on your SQL Server Edition, you'll either be using bitmap filter or optimized bitmap filter. Both help you, though the optimized one (Enterprise/Developer only) can do some pretty nifty things (elimnating rows -before- the join).

For more details, check out Optimizing Data Warehouse Query Performance Through Bitmap Filtering

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.