Myth, Group by always result in sorting the table on Group by Column

There are lot threads on various forum discussing automatic sorting behaviour of Group By clause, lets put extra mile to prove this myth incorrect.

So here is my query, I am running this dummy query on AdventureWorks2012 database

select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

group by pc.Name

Name                                               Sum of Sub Product Id

-------------------------------------------------- ---------------------

Accessories                                        378

Bikes                                              6

Clothing                                           172

Components                                         147

Let’s see from the prospective of execution plan,

From execution plan we can see the Group By physical operator is replaced by Stream Aggregate logical operator. And since Stream Operator always want its input to be in sorted order, we can see an existence of Sort operator in the execution plan which is sorting the ProductCategory data on ProductCategory.Name wise. And this is the reason why we get our result in sorted order when we use Group by clause our query.

Now just think about a situation where instead of Stream Aggregate, optimizer feels to use Hash Aggregate, which don’t require its input to be in sorted order.

Let’s try to bring Hash Operator in the plan in place of Stream Operator.

I am not able to stimulate the situation where Hash Aggregate appears in plan. So lets play a tricky game with Optimizer, and tell there is no such Stream aggregate operator present thus don’t make it use for creating plan

You can disable Stream Aggregate operator by

DBCC TRACEON (3604);

DBCC RULEOFF('GbAggToStrm');

GO

But later after your operation don’t forget to Rule on for Stream Aggregate operator.

DBCC TRACEON (3604);

DBCC RULEOFF('GbAggToStrm');

GO

select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

group by pc.Name

OPTION (RECOMPILE);

GO

DBCC RULEON('GbAggToStrm');

Name                                               Sum of Sub Product Id

-------------------------------------------------- ---------------------

Bikes                                              6

Clothing                                           172

Accessories                                        378

Components                                         147

Here we can see the result is didn’t get sorted by optimizer, lets see how optimizer executed this query.

We can see the Stream Aggregate operator replaced by Hash Aggregate which don’t require its input to be in sorted manner and that is the reason why our result resulted in unsorted manner.

So to be in safe side, if you want your group by data in sorted manner do use order by clause along with group by, this way you can guarantee your data to be in sorted manner.

 

DBCC TRACEON (3604);

DBCC RULEOFF('GbAggToStrm');

GO

select  pc.Name, sum( psc.ProductSubcategoryID) as 'Sum of Sub Product Id' from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

group by pc.Name

order by pc.Name

OPTION (RECOMPILE);

GO

DBCC RULEON('GbAggToStrm');

Name                                               Sum of Sub Product Id

-------------------------------------------------- ---------------------

Accessories                                        378

Bikes                                              6

Clothing                                           172

Components                                         147

Conclusion: Always use order by along with Group By, if you want your data to be in sorted manner.

 

SQL Server with a Tea glass makes a perfect combination to deal with darkness :)

Post Reference: Vikram Aristocratic Elfin Share

Group by clause sort my record set on Group by specified column

We must have experience that whenever we apply group By clause, it automatically sort the record set on the column specified by Group By clause. Lets see whats the basic behind the sorting technique.

Lets fire a query on the AdventureWorks2014 database ProductCategory and ProductSubCategory table.

select  pc.Name, sum( psc.ProductSubcategoryID) from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

group by pc.Name

Name                                              

-------------------------------------------------- -----------

Accessories                                        378

Bikes                                              6

Clothing                                           172

Components                                         147

Here we can see the records are appearing in sorted order. Lets see the execution plan to check what is happing at the background.

The execution plan above is clearly shown a sort operator which is why we are get our data sorted by Name column. Lets dissect the plan to see how it has executed the complete query.

The series of Logical operation that happened are

1.        First, Clustered Index Scan on ProductionSubCategory to get all the data from the table.

2.        Second, Clustered Index Scan on ProductCategory to get all the data from the table.

3.        Third, Sort operator, which was applied on ProductCategory table to get the data in sorted manner. Which internally do Order By on ProductCategory.Name column.

4.        Forth, then inner join was implemented to get the matching records from both the table.

5.        Fifth, Stream Aggregate was performed on the data which group by data on the bases of PorductCategory.Name column.

So here we can easily say why group by is producing result in sorted manner. because of sorting of ProductCategory table on ProductCategory.Name Column which is internally happening.

Reason for Sort Operator appearance, the stream aggregate operator always wants its input in sorted order before it apply its operation of aggregating.

Lets do one more practical to remove the sort operator, I am interested to remove this because it s taking the most cost among all the operator.

So internally sort operator arrange the data, the samething we can accomplish through indexing also, the benefit will be along with sorting the access to the data from ProductCategory tabke will be faster.

So why wait lets create Non Clustered Index on Name column of ProductCategory.

create unique nonclustered index [ak_productcategory_name] on [production].[productcategory]

(

       [name] asc

)

Command(s) completed successfully.

So now we have index on ProductCategory Name column. Now its time to rerun the same query to see whether we are still getting the sort operator in execution plan.

select  pc.Name, sum( psc.ProductSubcategoryID) from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

group by pc.Name

So we are successful in removing the sort operator and we can see the Index we created on Name column is now appears in execution plan.

And since index automatically sort the data, optimizer didn’t make use of sort operator explicitly to do the sorting and thus we can see a good plan.

 SQL Server with a Tea glass makes a perfect combination to deal with darkness J

Post Reference: Vikram Aristocratic Elfin Share

Difference between COUNT and BIG_COUNT from the angle of execution plan

We all know when we need to find the the count of rows in a table which exceed the int datatype range, we use BIG_COUNT instead of COUNT, which has return type bigint unlike int in case of COUNT.

Let’s see from the prospective of execution plan, how it differ in plan

select count(*) from Production.ProductCategory pc 

Lets see the text plan to see, what optimizer in detail did to execute the above query.

SET STATISTICS PROFILE ON

select count(*) from Production.ProductCategory pc 

Again a very simple plan, lets go through with each step to see how optimizer executes the query. First step, all rows are read from NonClustered Index Scan, then Stream Aggregate counts the number of rows in the record set and place the record count in Expr1002. Then Compute Scalar Operator takes the record count stored in Expr1002 and convert it to INT Datatype and kept the result in Expr1001.

The output of the Stream Aggregate operator  is a BigInt, but as we know the COUNT aggregate function returns value of type INT, so it use Compute scalar to convert result of count in INT data type. You may remember that, in order to count BigInt values, you should use the COUNT_BIG function. If you change the query above to use the COUNT_BIG, then you will see that the plan no longer uses the compute scalar operator

Lets check out the same query with BIG_COUNT.

select count_big(*) from Production.ProductCategory pc 

 

Here we can see there is no Compute scalar operator in the execution plan. Lets see now whether this plans makes any difference in cost compared to query using COUNT.

Here if we compare the final tree cost the query using BIG_Count has 0.0032893 where as query using COUNT has  0.0032893. So it came out as same. So it makes no or minimal cost change while using count vs big_count.

Conclusion: BIG_COUNT don’t use compute Scalar Operator because the Stream Aggregate operator of COUNT returns result in BIG INT datatype where as in case of COUNT function, since it has return type of INT, the compute scalar operator is used to convert the result returned from  Stream Aggregate from big int to int.

 

SQL Server with a Tea glass makes a perfect combination to deal with darkness :)

Post Reference: Vikram Aristocratic Elfin Share