SQL Server Optimizer and The partial aggregate, GenLGAgg e LocalAggBelowJoin rules

Hi Guys,
Welcome back!

I hope you enjoy this new post.
Today i will show you others interesting facts about the SQL optimizer.
Are you ready to enter in the detail of the partial aggregate?

Introduction

The execution plan produced by a query depends a lot from data.
This in because the optimizer is able to use both cardinality estimate and statistics in order to improve the execution plan.

If you remember, is what we saw in the last posting when we was talking about the cost based optimization plan and the rules.

Well, we continue today to talk about its operation logic!

 

The partial aggregate rules

So, we find ourselves today with the same query of the last post:

       
SELECT
  ANNDOC, NUMDOC, SUM(R.QTA1) AS SUM_QTA
FROM ORDTES T
  JOIN ORDRIG R ON R.IDORDTES = T.ID
WHERE
  ANNDOC = 2018
GROUP BY ANNDOC, NUMDOC
ORDER BY ANNDOC, NUMDOC

Our Query have the simple execution plan below:

Now look what happens!

I add a non clustered index on the ANNDOC column with an include on the NUMDOC column:

       
CREATE NONCLUSTERED INDEX IDX_ANNDOC_INCLUDE_NUMDOC
ON [dbo].[OrdTes] ([ANNDOC])
INCLUDE ([NUMDOC])

Remember that doing so we create also a statistic associated to the index.

So let’s check it with the T-SQL command:

       
DBCC SHOW_statistics ('OrdTes', IDX_ANNDOC_INCLUDE_NUMDOC)
 

The results returned are theese:

This is an important concept:the optimizer “knows the number of the rows after the where clause” before reading any data

In this way the optimizer is able to push down an hash match operator immediately after the Clustered index scan on the table ORDRIG.
Pushing down an hash match operator we have split the aggregation in two parts and this is called Partial aggregation.

Infact if you now look to the new execution plan generated we have two hash match!

 

We can say that in this case that the SQL optimizer use a partial aggreate. (in yellow you can read the word partialagg in the foto above)

Now if we look to the rules exercized: 
 

We have that:

• The query optimizer  use a rule called GenLGAgg for “Generate Local and Global Aggregate”. This rule split ‘ordinary’ aggregates into local and global parts.

• The rule to explore the option of pushing a local aggregate below a join. It's name is LocalAggBelowJoin.

Thats'all for today dear readers,
See you soon!

Luca Biondi @ SQLServerPerformance blog!

 

 

 

 

 

Previous post: Inside the SQL Server Query Optimizer - part 5 The cost based optimization process and the Rules

Tune of the post: Fantasy by Oliver Onions