Query Plan Example 2

By: Tibor Nagy

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.