THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand.

Inside the Optimizer: Constructing a Plan - Part 2

Summary: Continuing the series of posts looking at how the optimiser matches and applies internal rules to refine a query plan.


This post is part of a series: Part1 Part 2 Part3 Part4

The last entry ended with this query plan:

Nonclustered-Scan

The optimiser has pushed the predicate “ProductNumber LIKE 'T%'” down from a Filter iterator to the Index Scan on the product table, but it remains as a residual predicate.  We need to enable a new transformation rule (SelResToFilter) to allow the optimiser to rewrite the LIKE as an index seek:

After-SelResToFilter

 

Notice that the LIKE is now expressed in a SARGable form, and the original LIKE predicate is now only evaluated on rows returned from the seek.

The remaining inefficiency is in scanning the whole Inventory table index for every row returned by our new seek operation.  At the moment, the JOIN predicate (matching ProductId between the two tables) is performed inside the Nested Loops operator.  It would be much more efficient to perform a seek on the Inventory table’s clustered index.

To achieve that, we need to do two things:

  1. Convert the naive nested loops join to an index nested loops join (see Understanding Nested Loops Joins)
  2. Drive each Inventory table seek using the current value of Product.ProductId

The first one is achieved by a rule called JNtoIdxLookup.  The second requirement is a correlated loops join - also known as an Apply.  The rule needed to transform our query to that form is AppIdxToApp.

With those two rules available to the optimiser, here’s the plan we get:

Index-Apply

We’re now pretty close to the optimal plan (for the specific value in this query).  The last step is to collapse the Compute Scalar into the Stream Aggregate.  You might remember that the purpose of the Compute Scalar is to ensure that the SUM aggregate returns NULL instead of zero if no rows are processed.

As it stands, the Compute Scalar is evaluating a CASE statement based on the result of a COUNT(*) performed by the Stream Aggregate.  We can remove this Compute Scalar, and the need to compute COUNT(*), by normalising the GROUP BY using a rule called ‘NormalizeGbAgg’.  Once that is done, we have the finished plan:

Final-Plan

In the next posts in the series, I’ll show you how to customise the rules available to the optimiser, and explore more of the internals of query optimisation.

© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Published Thursday, July 29, 2010 7:42 PM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Davide Mauri said:

"In the next posts in the series, I’ll show you how to customise the rules available to the optimiser, and explore more of the internals of query optimisation."

Can't wait! :)

July 29, 2010 6:01 AM
 

Paul White said:

Thanks Davide!  You won't have long to wait.

July 29, 2010 6:29 AM
 

Jeff Moden said:

Hi Paul,

Just curious... the final execution plan on this page has two nice index seeks... what does the code actually look like for that?

--Jeff Moden

July 29, 2010 8:53 AM
 

Paul White said:

Hey Jeff,

If you're after the T-SQL, you'll find it in my previous post: http://sqlblog.com/blogs/paul_white/archive/2010/07/29/inside-the-optimiser-constructing-a-plan.aspx

Thanks for stopping by - it's always a pleasure to get your feedback!

Paul

July 29, 2010 9:51 AM
 

Dan Halliday said:

Hi Paul

I'm curious how you managed to coerce to optimiser into some of the sub-optimal plans. I could see how I could force some, but not most. Do you have a record of the settings & hints you used for each of these at all please?

Thanks

Dan

July 29, 2010 10:24 AM
 

Dan Halliday said:

Whoops - apols. Just read the comments on the other post. I'll stay tuned!

July 29, 2010 10:25 AM
 

Paul White said:

Hi Dan,

No worries.  All will (start to) be revealed in my next post!

All feedback is useful - it seems I could have been clearer on this point.  I'll bear it in mind.

Paul

July 29, 2010 10:32 AM
 

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan ??? Part 4 said:

August 10, 2010 6:23 PM
 

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan ??? Part 3 said:

August 10, 2010 6:24 PM
 

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan - Part 2 said:

August 10, 2010 6:25 PM
 

Page Free Space: Paul White : Inside the Optimiser: Constructing a Plan - Part 1 said:

August 10, 2010 6:25 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement