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. See also my articles on SQLperformance.com

Inside the Optimizer: Constructing a Plan – Part 3

Summary: This post presents an undocumented Dynamic Management View we can use to identify the optimisation rules involved in producing an executable plan.


This post is part of a series: Part1 Part 2 Part3 Part4
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 of that information before running a test query, and comparing it with the post-query DMV data, we can deduce the rules invoked by the optimiser for that query.

Before we get to the DMV itself, we need to nail 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 process that starts at the root of the logical operator tree, and ends with a physical representation suitable 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 approach would guarantee 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 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 how promising the rule is (in context).  A rule has a higher promise value if it has a high potential 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 strategy.  This helps reduce compilation time, while still pursuing the most promising transformations.

sys.dm_exec_query_transformation_stats

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

DMV-Info

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 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 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 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 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 occurs.  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:

Final-Query-Plan

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

Sample-DMV-Results

 

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 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 any comments, ideas or feedback you may have, so I can improve future posts.

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

Published Saturday, July 31, 2010 5:11 AM 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

 

Adam Machanic said:

Great post! Learning some new tricks :-)

July 30, 2010 12:22 PM
 

Fabiano Amorim said:

That is awesome a lot of very good information here… keep on with the wonderful stuff… looking forward to read the next post…

Cheers

July 30, 2010 9:25 PM
 

Davide Maurid said:

Really cool! Now I'm really looking forward to have power on optimizer rules! :)

July 31, 2010 3:29 AM
 

Paul White said:

Thanks everyone (especially Adam!)

The next post in the series is up now.

David: remember that with power comes responsibility! :cD

July 31, 2010 4:07 AM
 

foman said:

Great series!

so many rules are applyed,so it needs to cache the compilation result of queries for saving optimising time.

August 5, 2010 8:57 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