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.

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.

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

• Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216
• SQL SERVER – Parallelism for Heap Scan
• Microsoft Dynamics CRM – Max Degree of Parallelism Settings and Slow Performance
• SQL SERVER – Relationship with Parallelism with Locks and Query Wait – Question for You
• SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28
• SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
• SQL SERVER – Find Queries using Parallelism from Cached Plan

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)