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()...
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.