SQL Server Optimizer – Under the Hood Part 1

SQL Server Optimizer, Under the Hood – Partial Aggregates

Abstract: In this series of short articles, we lift the hood of the SQL Server Optimizer to examine a few of the many clever tricks used to optimize query performance.  You’ll see that the Optimizer does not limit itself to using only the instructions provided by the written query syntax, but can correctly deduce a more efficient approach from information it gleans from the schema, the query and data statistical information.

In Part I, we look at partial aggregate operators – an extremely clever way of downsizing certain large joins into much smaller (and faster) joins.

In my previous series of articles, we examined the three different physical join operators (Nested Loops, Merge and Hash Match) and saw how the Optimizer chooses which is best to use for different types of queries and for varying data patterns. In this series, we will take things one step further and describe some of the other tricks the Optimizer has stored up its sleeve for us when it comes to optimizing query performance. The first sleight-of-hand, known as partial aggregation, is a technique commonly used for joins with particular characteristics. An example follows.

Note : To execute this article’s demo code you will need to download the AdvertureWorks2008R2 database from CodePlex. However, this article is written to be a full explanation without using the sample database or code.

So without further ado, let’s jump right in to the mysterious maze of the Optimizer’s mind.

Consider the following query:

USE AdventureWorks2008R2
GO
-- Number of Orders Per Sales Person Name
SELECT		P.FirstName,
		P.LastName,
		COUNT(*) AS [Number Of Orders]
FROM		Person.Person AS P
		INNER JOIN
		Sales.SalesOrderHeader AS SOH
		ON P.BusinessEntityID = SOH.SalesPersonID
GROUP BY	P.FirstName,
		P.LastName
ORDER BY	[Number Of Orders] DESC
GO

The query itself is quite simple and straightforward. It produces a list of names of sales people and their order counts, arranged so that the highest-producing sales person name is listed first.

It joins the Person table with the SalesOrderHeader table, groups the result by the sales people’s first name and last name, and counts the number of orders each sales person has obtained. Note that there is a potential logical issue with this query as grouping by first name and last name does not guarantee uniqueness of sales person because two sales people may share the same name.  This may lead to potentially confusing results when “John Smith” is only listed once in the result set, and “his/hers” sales look much larger than either of them expected. But because the AdventureWorks database does not contain identical first and last names, we’ll ignore this issue for now.

At first glance, we might expect the Optimizer to take a straightforward approach to execute this simple query: 

1.       Join the two tables.

2.       Group by the first name and last name columns.

3.       Count the number of orders in each group.

Obvious enough, isn’t it?

Well, let’s take a look at the actual execution plan that was used, in the figure below.

Figure 1 – Actual Execution Plan

The execution plan seems to be exactly as expected, except for the Stream Aggregate operator [2] which follows the Index Scan operator [1] on the SalesOrderHeader Table. What is it doing there? It can’t aggregate order counts by First Name and Last Name quite yet… the First Name and Last Name won’t be available until after the join is complete, because those columns are from the Person table which has not been accessed yet… What the heck is going on? 

Continues…

Pages: 1 2




Related Articles :

  • No Related Articles Found

One Response to “SQL Server Optimizer – Under the Hood Part 1”

  1. Hi Amy,

    Great article! good insights of this shortcut.
    Of course we didn’t lose 1 sales person :-) I think it’s because the way of dealing of nulls.

    According to the statistics of SalesOrderHeader (DBCC SHOW_STATISTICS(‘Sales.SalesOrderHeader’,'IX_SalesOrderHeader_SalesPersonID’))

    the histogram is divided into 18 steps. One of these steps (the fist) is showing the statistics about he columns where sales personID is null. The Partial Aggregation is counting these nulls also as one group.

    The where clause doesn’t, so we end up with 17 groups. :-)

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |