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
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:
- 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.
- 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.
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 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)
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
OPTION (KEEPFIXED PLAN);
-- Query under test
-- Must use OPTION (RECOMPILE)
total_qty = SUM(I.Quantity)
FROM Production.Product P
JOIN Production.ProductInventory I
ON I.ProductID = P.ProductID
WHERE P.ProductNumber LIKE N'T%'
promise = QTS.promised - S.promised,
WHEN QTS.promised = S.promised
(QTS.promise_total - S.promise_total) /
(QTS.promised - S.promised)
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
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:
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 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.
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