SQL Server aggregate operator – Stream Aggregate Part1

Hi Friends,

Today, we will have a look into SQL Server aggregate operator – Stream Aggregate. This operator used to group some rows by one or more columns and to calculate any aggregation expressions specified in query statement(s).

Some common types of expressions are SUM, AVG, COUNT, MAX, MIN etc. When we use any of these functions we will probably see a stream aggregate operation on the plan. This operator can be very fast as it requires an input that already been ordered by the column specified in GROUP BY clause else it may force query optimizer to use SORT operator or can use a pre-sorted data from index seek or scan.

Enough theory? Ok, let us see this in action using following statement;

USE [AdventureWorks2012]

SELECT AVG(Product.SafetyStockLevel)
FROM [Production].[Product]

StreamAggregatePlan

StreamAggregateToolTip

To understand what Expr1004 and Expr1005 are, let us decode query plan in text.

USE [AdventureWorks2012]

--Step 1
SET SHOWPLAN_TEXT ON

--Step 2
SELECT AVG(Product.SafetyStockLevel)
FROM [Production].[Product]

--Step 3
SET SHOWPLAN_TEXT OFF

StreamAggregateTextPlan

We can obtain same information from graphical plan by selecting Stream Aggregate operator then pressing F4.

StreamAggregateProperties

In order to calculate AVG, the stream aggregate is computing both count and sum and the results are stored in the computed expressions Expr1004 and Expr1005 respectively. Compute scalar operator (we are going to cover this operator in future) verifies there is no division by zero using a case statement.

We are going to discuss more on this operator on tomorrow’s post, stay tuned.

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *