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

Introduction

In order to fully explore the way the query optimiser uses rules to construct plan alternatives, we will need a way to identify the rules used to optimise a particular query.

SQL Server 2005 (later builds only) and SQL Server 2008 include an undocumented Dynamic Management View (DMV) that shows information about the internal rules used by the optimiser.  By taking a snapshot(n. 快照，快相；急射，速射；简单印象) of that information before running a test query, and comparing it with the post-query DMV data, we can deduce(vt. 推论，推断；演绎出) the rules invoked by the optimiser for that query.

Before we get to the DMV itself, we need to nail(vt. 钉；使固定；) down a few more things about the internals of the SQL Server query optimiser.  The next section builds on the ‘trees and rules’ information given in part 1 of this series.

The Optimisation Process

Query optimisation is a recursive(adj. [数] 递归的；循环的) process that starts at the root of the logical operator tree, and ends with a physical representation suitable(adj. 适当的；相配的) for execution.  The space of possible plans is explored by applying rules which result in a logical transformation of some part of the current plan, or a conversion to a physical implementation.

The optimiser does not try to match every available rule to every part of every query, in every possible combination.  That sort of exhaustive(adj. 详尽的；彻底的；消耗的) approach would guarantee(n. 保证；担保；) the best plan possible, but you would not like the compilation times, or memory usage!

To find a good plan quickly, the optimiser uses a number of techniques.  I plan to cover these in some detail in future posts, but two of these tricks are immediately relevant to the DMV:

1. Every operator in the logical tree contains code to describe all the rules that are capable(adj. 能干的，能胜任的；有才华的) of matching with it.  This saves the optimiser from trying rules which have no chance of producing a lower-cost plan. 
2. Every rule contains code to compute a value to indicate(vt. 表明；指出；预示；象征) how promising the rule is (in context).  A rule has a higher promise value if it has a high potential(adj. 潜在的；可能的；) to reduce the cost of the overall plan.  In general, commonly-used optimisations (like pushing a predicate) have a high ‘promise’ value.  More specialised rules, like those that match indexed views have a lower promise value.

When faced with several possible rule choices, the optimiser uses promise values as part of a pruning(n. 修剪；剪枝) strategy. This helps reduce compilation time, while still pursuing(v. 追求；追赶) the most promising transformations.

sys.dm_exec_query_transformation_stats

This DMV contains one row for each rule, with the following columns:

The name column contains the internal name for the rule.  An example is ‘JNtoSM’ – an implementation rule to transform a logical inner join to a physical sort-merge join operator.

The promised column shows how many times the rule has been asked to provide a promise value to the optimiser.

The promise_total column is a simple sum of all the promise values returned.

The promise_avg column is just promise_total divided by promise.

The built_substitute column tracks how many times the rule has produced an alternative implementation.

The succeeded column tracks the number of times that a rule generated an transformation that was successfully added to the space of valid alternative strategies.  Not all transformations that result in an alternative will match the specific requirements of the current query plan (for example, the alternative may not preserve(vt. 保存；保护；) a required sort order, or some other property).

Using the DMV

The scripts included here were tested on SQL Server x86 Developer Edition, versions 10.0.2775 (2008 SP1 CU8) and 9.0.4294 (2005 SP3 CU9).  This DMV may not be available on all builds of SQL Server 2005.

Since the DMV contains server-scoped optimiser information, for correct results you will need to ensure that no other concurrent(adj. 并发的；一致的；同时发生的) optimisation activity is occurring on the test server.  Working on a personal test SQL Server and ensuring yours is the only active connection is a good place to start with that.

First, we need to create the structure of a temporary table to hold our snapshot of the DMV contents prior(adj. 优先的；在先的，在前的) to running our test query:

SELECT  TOP (0)
        name,
        promise_total,
        promised,
        built_substitute,
        succeeded
INTO    #Snapshot
FROM    sys.dm_exec_query_transformation_stats;

Now we can write a batch to capture(vt. 俘获；夺得；) a DMV snapshot, run our test query, and show the DMV differences afterward:

-- Clear the snapshot
TRUNCATE TABLE #Snapshot;
 
-- Save a snapshot of the DMV
INSERT  #Snapshot 
        (
        name, 
        promise_total, 
        promised, 
        built_substitute, 
        succeeded
        )
SELECT  name, 
        promise_total,
        promised, 
        built_substitute, 
        succeeded
FROM    sys.dm_exec_query_transformation_stats
OPTION  (KEEPFIXED PLAN);
 
-- Query under test
-- Must use OPTION (RECOMPILE)
SELECT  P.ProductNumber, 
        P.ProductID, 
        total_qty = SUM(I.Quantity)
FROM    Production.Product P
JOIN    Production.ProductInventory I
        ON  I.ProductID = P.ProductID
WHERE   P.ProductNumber LIKE N'T%'
GROUP   BY
        P.ProductID,
        P.ProductNumber
OPTION  (RECOMPILE);
 
-- Results
SELECT  QTS.name,
        promise = QTS.promised - S.promised,
        promise_value_avg = 
            CASE
                WHEN QTS.promised = S.promised
                    THEN 0
                ELSE
                    (QTS.promise_total - S.promise_total) /
                    (QTS.promised - S.promised)
            END,
        built = QTS.built_substitute - S.built_substitute,
        success = QTS.succeeded - S.succeeded
FROM    #Snapshot S
JOIN    sys.dm_exec_query_transformation_stats QTS
        ON QTS.name = S.name
WHERE   QTS.succeeded != S.succeeded
ORDER   BY
        promise_value_avg DESC
OPTION  (KEEPFIXED PLAN);

The query to test must have the OPTION (RECOMPILE) query hint added to ensure that a compilation(n. 编译；编辑；汇编) occurs(vi. 发生；出现；存在).  Other queries in the batch have OPTION (KEEPFIXED PLAN) to help avoid compilations that would skew the results.

The example above uses the AdventureWorks query we have been using in this series so far.  It produces the familiar, fully-optimised, plan:

Here are the (partial) results from a typical run:

 

The output shows the rule name, the number of times a promise value was calculated, the average promise values produced, the number of times a transformed structure was built, and the number of times the new structure was successfully added to the optimiser’s list of valid choices.

Notice that rules can be invoked multiple times, since they may match more than one place in the query, and the compilation process is a recursive(adj. [数] 递归的；循环的) one.  You might also see rules with a promise value of zero, which simply means the promise-calculating code did not have enough information to produce a value.

Next time

Now that we have found a way to identify the rules used to optimise a given query, we can move on to the really fun stuff.  In the next part of the series, I will show two ways to affect the rules available to the optimiser, and present code to reproduce the ‘interesting’ partially-optimised query plans shown in parts 1 & 2.

In the meantime, I would appreciate(vt. 欣赏；感激；领会；鉴别) any comments, ideas or feedback you may have, so I can improve future posts.