Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!!

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
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 '12 at 16:15
1  
This is a fantastic resource! You probably just saved me from posting another dozen questions! :) –  JHFB Mar 6 '12 at 16:17
    
Agreed, I can't believe it's free. Thank you Redgate :) –  Eric Higgins Mar 6 '12 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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