Why are there 2 stream Aggregate operators?

vfrank66 2016-11-10 13:21:12

I am new so maybe I need more detail. This will be my first post so bare with me.

Vlady Oselsky 2016-11-10 15:21:40
This is a result of Parallelism. If you look at first Stream Aggregate right after Clustered Index Scan you will see two arrows in the corner. This means that SQL Server will run parallel threads to get data and then after all 4 streams are grouped together it will run another aggregate function on it to give you the final result.

alt text

Fo the test you can add an option to restrict it to a single thread to see new plan. To do so just add MAXDOP 1 to your query in the following matter and run it again.

SELECT MAX(gar.DateStamp)
FROM TRSDev0203.dbo.GenActRec AS gar
OPTION (MAXDOP 1)
SQLkiwi 2016-11-16 06:35:49
To efficiently run the MAX operation on multiple threads, SQL Server:

  1. Uses parallel scan to distribute rows from the Clustered Index Scan across among multiple threads
  2. Computes the maximum value seen on each thread using the parallel Stream Aggregate
  3. Gathers the parallel thread results together, resulting in one maximum per thread.
  4. The final, serial Stream Aggregate computes the maximum of the local maximums computed on each thread.

The general pattern is called local-global aggregation. Each thread computes the aggregate on the rows it sees (locally) then a small final global aggregate computes the final (global) result from the local results.

An alternate name for the local aggregate is a partial aggregate. You can see that the parallel Stream Aggregate computes a partial result by inspecting the properties:

alt text

For more technical details, see Partial Aggregation by Craig Freedman.

For more on parallel execution, see my article Understanding and Using Parallelism in SQL Server.