Forum Discussion
SQL Server Stream Aggregate vs Hash match Aggregate
Hello, I am a new SQL Server engineer.
While looking at the execution plan of a query that SUM() values of int type based on the range of a date column of datetime type, I discovered that SUM() processing is handled by Stream Aggregate and Hash match aggregate in each case.
The table is composed of a Clustered Index with Date as the key. I think it changes to Hash match processing as the index search range increases, but I don't know the difference between the two processing methods and what criteria determine the processing method. Can you give me a good answer?
1 Reply
SQL engine make decision depends on several factors,
- Data sorted. When the data will be aggregate is pre-sorted in the order of the GROUP BY/aggregation key (e.g., due to a clustered index on the grouping column). The engine might choose Stream Aggregate it's easy to collect data by sorted key.
- Parallelism and Grouping count. If data is sorted, but there has much grouping count to aggregate, Stream Aggregate is not efficient, it processes only a single group at a time. Hash Match Aggregate might be chosen when the parallelism is required.
- Memory. Hash Match Aggregate need maintain hashtables, memory cost is higher.
