SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism?

Is Stream Aggregate is Same as Gather Streams of Parallelism? – This was one of the brilliant questions I had received during my recent consulting engagement of Comprehensive Database Performance Health Check. They are very different things even though the name may sound familiar to some.

Stream Aggregate is NOT the same as Gather Streams of Parallelism.

SQL SERVER - Is Stream Aggregate is Same as Gather Streams of Parallelism? stream-800x532

Stream Aggregate

When you perform any kind of aggregation on input like SUM, AVG, MAX, MIN or any such aggregation, SQL Server may use stream aggregator to merge all the input to a lesser number of input.

As stream aggregate requires sorted data as input, it is often spotted with Sort operation.

Gather Streams – Parallelism

When SQL Server uses parallel query plans, it eventually has to gather all the multiple input streams to a single output stream and that is the time Gather Streams operation operator shows up in the parallelism.

This operator is only visible when parallelism is enabled for a query.

Both Together – Stream

It is totally possible to spot both of these operators in a single query. Here is the query which I have written for the sample database WideWorldImporters.

SELECT COUNT(*), i.RunPosition
FROM [Sales].[Invoices] i
GROUP BY i.RunPosition
ORDER BY i.RunPosition

When you run the query, it produces the execution plan with both the operators in it.

SQL SERVER - Is Stream Aggregate is Same as Gather Streams of Parallelism? operators

Here are a few more blog posts which you may find interesting.

Did you know the difference between two parameters? I would love to know your feedback in the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Limitation of ENABLE_PARALLEL_PLAN_PREFERENCE Hint
Next Post
SQL SERVER – Simple Example of READPAST Query Hint

Related Posts

Leave a Reply

Menu