Query Plan Example 2

Overview

Let’s walk through a bit more complex query using the AdventureWorksDW database.

Explanation

Let’s filter the products table based on ReorderPoint < 800, group by Color and only show records having more than 10 members.

So our query looks like this:

SELECT Color, COUNT(DISTINCT ProductKey)
FROM AdventureWorksDW..DimProduct
WHERE ReorderPoint < 800
GROUP BY Color
HAVING COUNT(DISTINCT ProductKey) > 10
ORDER BY Color

Open a new query window and press CTRL+M to include the Actual Execution Plan and then execute the above query.

The Actual Query Plan for this query is shown below.

You can see in the above image that SQL Server processed the query in a linear way and note the cost percentage value under each operator icon. 

So the steps for this plan are:

• Clustered Index Scan on the DimProduct table (cost 88%) (get data where ReorderPoint < 800)
• Sort by Color using the sort operation (cost 11%) (sort by color)
• Stream Aggregate operation (cost 0%) (group by color for count)
• Compute Scalar operation (cost 0%) (count)
• Filter the results (cost 0%) (having count > 10)
• Select to return the result set (cost 0%) (return data)

Just a reminder: we should look for the following items to pin down the possible improvements for this query:

• resource intensive operations where the percentage is high
• scan operations and
• fat bars between operators (the fatter the bar the more data)

It is obvious that the most resource intensive operation is the Clustered Index Scan on the DimProduct table that uses 88% of all the resources and passes a large number of rows to the next operation.

So how can we improve the performance? 

For this one it is pretty easy since SSMS shows us a Missing Index hint in green. If you right click on the green index hint a menu will pop up.  Select Missing Index Details…

A new query window will open with the following code.  It advises us to create an index on the ReorderPoint column and include the Color column in the index.  Based on this new index, the query should do an Index Seek operation and we can speed up the query by 86% according to the Query Optimizer estimation.

Tibor Nagy

I am a technical and business professional based in Budapest, Hungary. I am working in the financial industry on the operations side, but also have test management experience in the educational software domain. My experience includes Microsoft SQL Server 2000-2012, IBM DB2 and MySQL database administration, troubleshooting, migration and development. My focus points are performance tuning and security. I am passionate about sharing my knowledge with the greater audience.

I hold MSc degree in Engineering and Management. I am married and I have a beautiful daughter.

LinkedIn account:
http://www.linkedin.com/in/nagytibor

Twitter account:
https://twitter.com/#!/tnagy_eeft