The Transact-SQL scalar aggregation operation can be done in serial or in parallel.
In a parallel scalar aggregation, the aggregation operation is performed in two phases, using two scalar aggregate operators. In the first phase, the lower scalar aggregation operator performs aggregation on the data stream. The result of scalar aggregation from the first phase is merged using a many-to-one exchange operator, and this stream is aggregated a second time.
In case of a count(*) aggregation, the second phase aggregation performs a scalar sum. This is highlighted in the showplan output of the next example.
select count(*) from RA2
QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. 5 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped SUM OR AVERAGE AGGREGATE. | | |EXCHANGE Operator (Merged) | |Executed in parallel by 2 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |SCALAR AGGREGATE Operator | | | | Evaluate Ungrouped COUNT AGGREGATE. | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | RA2 | | | | | Table Scan. | | | | | Forward Scan. | | | | | Positioning at start of table. | | | | | Executed in parallel with a 2-way partition scan. | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | With LRU Buffer Replacement Strategy for data pages.
Adaptive Server may also choose to do the aggregation in serial. If the amount of data to be aggregated is not enough to guarantee a performance advantage, a serial aggregation may be the preferred technique. In case of a serial aggregation, the result of the scan is merged using a many-to-one exchange operator. This is shown in the example below, where a selective predicate has been added to minimize the amount of data flowing into the scalar aggregate operator. In such a case, it probably does not make sense to do the aggregation in parallel.
select count(*) from RA2 where a2 = 10
QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. 4 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped COUNT AGGREGATE. | | |EXCHANGE Operator (Merged) | |Executed in parallel by 2 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RA2 | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Executed in parallel with a 2-way partition scan. | | | | Using I/O Size 2 Kbytes for data pages. | | | | With LRU Buffer Replacement Strategy for data pages.
