Parallelism in Merge Loop
Parallelism in Merge Loop
By : Kasim Wirama, MCDBA
This posting, I would discuss about parallelism in merge loop operator and its impact to performance. Parallelism involves two or more threads. And in merge join for single thread, the algorithm works with joining 2 input sets that are in sorted order and then a row in left input sets will move to next one if its value is less than rows in right input sets. If you need to know the explanation for merge loop, you can read my past posting about algorithm for nested loop, merge join and hash join. Welcome to my SQL Server knowledge centre.
Back to topic, how merge loop algorithm is implemented in multi threads. I could tell you that rows in both input sets will be distribute to a member of threads when 2 hash values of each input sets (for join criteria) are same. What algorithm is used for distributing these rows to related consumer threads? If you answer hash algorithm, yes it is correct, hash partitioning precisely. And it is merging type of parallelism so it is less scalable compared to non merging type of parallelism. Optimizer tries to avoid parallelism merge join otherwise it would choose the plan if you explicitly specify ordering with ORDER BY clause.
Merge join parallelism is less favourable join because it is potential to parallel deadlock. If you would like to know what parallel deadlock is, you can read my posting Merging, non-merging parallelism at http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2008/07/20/merging-non-merging-parallelism.aspx. Happy reading.