【SQL Server Inside the Optimizer】: Constructing a Plan - Part 2

The last entry ended with this query plan:

The optimiser has pushed the predicate “ProductNumber LIKE 'T%'” down from a Filter iterator(n. 迭代器；迭代程序) to the Index Scan on the product table, but it remains as a residual(adj. 剩余的；残留的) predicate.  We need to enable a new transformation rule (SelResToFilter) to allow the optimiser to rewrite the LIKE as an index seek:

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(n. 效率低；无效率；) 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(adj. 天真的,幼稚的) 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:

We’re now pretty close to the optimal plan (for the specific value in this query).  The last step is to collapse(vi. 倒塌；瓦解；) 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(v. 正常化（normalise的ing形式）) the GROUP BY using a rule called ‘NormalizeGbAgg’.  Once that is done, we have the finished 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.