Why are there 2 stream Aggregate operators?
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)
- Uses parallel scan to distribute rows from the Clustered Index Scan across among multiple threads
- Computes the maximum value seen on each thread using the parallel Stream Aggregate
- Gathers the parallel thread results together, resulting in one maximum per thread.
- 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:
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.