Hash Aggregate operator in SQL Server execution plan

In the query when we are using any aggregate function like SUM (), MIN (), MAX (), COUNT (), AVG () etc. Hash Aggregate operator appear in the execution plan. We can say that hash Aggregate Operator in SQL Server execution plans is used to perform aggregations on unsorted stream of data.

The Hash Aggregate operator does not require sorted input. Instead, it hashes the values it needs to aggregate and groups them in memory. It uses a hash table to group data by the aggregation key. For each row, it computes a hash value based on the key columns (e.g., the columns in a GROUP BY clause) and uses this to group the rows. Hash Aggregate operations are more memory-intensive than Stream Aggregate because it needs to store the rows in hash tables.  It is typically used for larger datasets where sorting would be too costly or inefficient.

Icon of Hash Match (Aggregate) Operator.

 

See the Example

Here I am creating a table without any index.

CREATE TABLE salesorderdetail   (      salesorderdetailid    INT IDENTITY(1, 1) NOT NULL,      salesorderid          INT NOT NULL,      carriertrackingnumber NVARCHAR(25) NULL,      orderqty              SMALLINT NOT NULL,      productid             INT NOT NULL,      specialofferid        INT NOT NULL,      unitprice             MONEY NOT NULL,      unitpricediscount     MONEY NOT NULL,      linetotal             NUMERIC(38, 6) NOT NULL   )

Let’s insert some records

INSERT INTO salesorderdetail             (salesorderid,              carriertrackingnumber,              orderqty,              productid,              specialofferid,              unitprice,              unitpricediscount,              linetotal) SELECT salesorderid,        carriertrackingnumber,        orderqty,        productid,        specialofferid,        unitprice,        unitpricediscount,        linetotal FROM   adventureworks2019.sales.salesorderdetail

Record inserted successfully.

Running the below query

select productid,count(*) from SalesOrderDetail group by productid

See the execution plan

Hash match (Aggregate) is generated by execution plan. See the cost of this operator is 34%.

Let’s create

CREATE INDEX IX_SalesOrderDetail ON SalesOrderDetail(productid);

Index is created successfully.

Let’s running the query and see the execution plan.

See Hash match (Aggregate) is converted as Stream Aggregate and also Cost has been decrease from 34 to 18 % almost it reduce more than 45 %.

We can convert Hash aggregate to Stream Aggregate by using proper index on the table.