Broadcast Algorithm in Distribute Stream Parallelism of SQL Server Execution Plan
Broadcast Algorithm in Distribute Stream Parallelism of SQL Server Execution Plan
By : Kasim Wirama, MCDBA
I have shown you round robin implementation in distribute stream. From performance perspective, round robin is less desirable type of parallelism because it implements no load balancing. But there is good type of parallelism, which is broadcast parallelism which I would explain further on this posting.
Basic knowledge of parallelism is that you need to know parallelism operator contains 2/more threads, which is internally contains 2 groups of operator composition, i.e., producer and consumer. For broadcasting in distribute stream parallelism, all rows will be sent from each of producer to all consumers. Parallelism on consumers side will impact on parallel scan on the underlying tables.
I take script example from “Parallelism in Nested Loop” posting (http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2008/07/12/parallelism-in-nested-loop.aspx). After that, turn on graphical execution plan toolbar, and execute the following query here :
select a.col1, a.col2, a.col3, b.col2, b.col3
from bigtable a
join anotherbigtable b
on a.col2 = b.col2
where a.col1 = 0
Execution plan for the query is :
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[anotherbigtable].[col2] AS [ b ].[col2]=[tempdb].[dbo].[bigtable].[col2] AS [ a ].[col2]))
|--Parallelism(Distribute Streams, Broadcast Partitioning)
| |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[bigtable].[PK__bigtable__182C9B23] AS [ a ]), SEEK:([ a ].[col1]=(0)) ORDERED FORWAR(D)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[anotherbigtable].[PK__anotherbigtable__5EBF139D] AS [ b ]))
See the execution plan above, all rows from outer query (bigtable) will be sent under broadcast partitioning parallelism context to inner query (anotherbigtable) and there is no OUTER REFERENCE in Nested Loops operator. So threads in inner query will cooperate among another to scan underlying table which is anotherbigtable object. As I mentioned that parallelism in nested loop operator will be parallel only in outer query, serial plan is still applied on inner query. But it has exception when there is broadcast partitioning on distribute stream, so both outer and inner query are under parallelism operation, which is good for performance perspective.