Merging, Non-Merging Parallelism
Merging, Non-Merging Parallelism
By : Kasim Wirama, MCDBA
This posting, I delve deeper into parallelism operator in execution plan. Parallelism is knows as exchange operator. It is named as exchange operator because producer threads distribute rows into consumer threads. If you are interested to know more, let’s look into further explanation about internals of parallelism operator.
In case of one producer, rows will be sent to consumer threads and this type of parallelism is called distribute stream parallelism. In case of multiple producers, there might be single consumer thread or multi consumer threads. For single consumer thread, it is called gather stream parallelism, and for multi consumer threads, it is called repartition stream parallelism.
How rows are distributed in terms of repartition and distribute stream parallelism? There are 5 algorithms that might be applied into them. They are broadcast, round robin, hash, demand, and range algorithm. For broadcast and round robin algorithm implementation, let’s browse my previous posting (http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2008/07/20/round-robin-algorithm-in-distribute-stream-parallelism-of-sql-server-execution-plan.aspx and http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2008/07/20/broadcast-algorithm-in-distribute-stream-parallelism-of-sql-server-execution-plan.aspx). Hash algorithm works by getting hash value of one or more columns in each row that would be matched into build tables. To get sense of hash algorithm, let browse to my historical posting in http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2008/05/18/nested-loop-merge-join-and-hash-join-algorithm.aspx. Feel free to visit my SQL Server Knowledge Center. J Next algorithm is Demand partitioning. It works on parallel scan on partitioned table. Internally, producer threads will ask consumer threads for rows. Other algorithm is Range algorithm. It works by examining a column for each input rows to list of range of values.
In multiple consumer threads like repartition and gather streams parallelism, there will be merging exchange and distribute streams parallelism is categorized into non-merging exchange. What do merging and non-merging exchange means? Merging exchange is type of parallelism which each consumer threads will receive rows from producer threads in sorted order. Rows in sorted order will be implemented either by explicitly by SORT operator or implicitly by utilizing applicable index. Merging exchange has blocking nature and it is less optimized in performance aspect. In some cases, it will cause degrade performance. And sometimes, you will find out that a query will be deadlocked by itself. Such a deadlock is called parallel deadlock. The probability of parallel deadlock will increase when degree of parallelism is high. To detect this parallel deadlock, you query DMV sys.dm_os_waiting_task, and look at wait_type column. If wait_type contains CXPACKET in same session with different threads, it shows parallel deadlock. Solution for this issue is to lower down degree of parallelism or force to a serial plan or rewrite problematic query.
Not all parallelism is considered sub optimal, distribute streams with broadcast and parallel scan without round robin implementation are examples of good type of parallelism. With the knowledge in your hands, you could identify the suboptimal operator in parallelism and devise a better execution plan for database performance.