Inside the Optimizer: Constructing a Plan – Part 4

More undocumented ways to explore how the query optimizer works.

Documented Rule-Affecting Options

It might surprise you that most T-SQL users have written queries that disable one or more optimizer rules already.

Join hints { LOOP | HASH | MERGE | REMOTE } and the query hints shown below are all implemented by disabling one or more optimizer rules:

For example, a logical GROUP BY operation (including DISTINCT) can generally be physically implemented as either a Hash Aggregate or a Stream Aggregate.

The two implementation rules involved are GbAggToHS (Group By Aggregate to Hash) and GbAggToStrm (Group By Aggregate to Stream).

Normally both of these implementation options are available to the optimizer. It might consider one or both when optimizing a logical operator tree.

When the query hint OPTION (HASH GROUP) appears in a query, the GbAggToStrm implementation rule is disabled. This means that all GROUP BY operations are implemented by the GbAggToHS rule, and so appear as Hash Aggregates.

The join hint INNER MERGE JOIN works by disabling the implementation rules for nested loops and hash join. An interesting side-effect is that using a join hint makes the query act as if OPTION (FORCE ORDER) had also been specified.

If we want the optimizer to only consider using merge and hash join strategies for a query, we could use the query hint OPTION (MERGE JOIN, HASH JOIN).

This works by disabling the JNtoNL implementation rule (Join to Nested Loops), leaving the optimizer the choice of sort-merge or hash joins (rules JNtoSM and JNtoHS).

Hinting physical joins in a query hint does not add an implied OPTION (FORCE ORDER).

Undocumented Features

Warning: This information is provided AS IS and for educational purposes only. Never use these tools on production systems. No documentation or support is available.

Join hints and query hints don’t expose options to selectively enable or disable the full range of rules available to the optimizer.

We can, however, use a couple of undocumented DBCC commands and the undocumented dynamic management view sys.dm_exec_query_transformation_stats (covered in part 3) to explore the way the optimizer uses rules.

The techniques shown below work best in SQL Server 2008 and later, but will also function in SQL Server 2005 (with a number of important caveats). The specific versions used in writing this post were x86 Developer Editions 10.0.2775 (2008 SP1 CU8) and 9.0.4294 (2005 SP3 CU9).

Trace Flags and DBCC commands

As with other undocumented DBCC options, we need to enable trace flag 3604 for the current session, so that any output is returned to the client (the messages tab in SQL Server Management Studio, for example):

DBCC TRACEON (3604);

We can disable one or more optimizer rules using DBCC RULEOFF. This command takes one or more rule names (or numbers) as its parameters. To disable the rules that implement a logical join as a sort-merge or hash, we would execute:

DBCC RULEOFF('JNtoSM', 'JNtoHS');

To enable the rules again, we can use DBCC RULEON, with the same syntax:

DBCC RULEON('JNtoSM', 'JNtoHS');

Both RULEON and RULEOFF return confirmation messages when trace flag 3604 is on.

The direct effects on the optimizer only apply to the current session, but the (sub-optimal) plans produced will be cached as normal. This is another reason to only play with this stuff on a personal test system!

To reset to normal operation, manually re-enable any disabled rules, or simply disconnect and reconnect to the server. It is also a good idea to run DBCC FREEPROCCACHE to remove any sub-optimal plans from cache.

To see which rules are currently enabled or disabled, use the DBCC SHOWONRULES and DBCC SHOWOFFRULES commands. Neither of these commands take any parameters.

SQL Server 2005 Bugs

In SQL Server 2005, SHOWOFFRULES displays a list of rules that are ON, and SHOWONRULES displays rules that are OFF, which is actually quite funny.

DBCC SHOWONRULES also does not return any output unless you call DBCC SHOWOFFRULES immediately afterward. The list of disabled rules will be prefixed to the output of SHOWOFFRULES (which shows enabled rules, remember).

The list of disabled rules is also not formatted correctly in 2005. All rule names are concatenated without any separator.

Both commands work perfectly in SQL Server 2008.

Putting it all together

We now have all the tools we need to produce the partially-optimized plans seen in previous posts in this series.

We can use the sys.dm_exec_query_transformation_stats DMV to identify rules invoked by the optimizer, and the new DBCC commands to selectively disable them to see the effect on the final plan.

Here is our sample AdventureWorks query’s logical relational tree representation again:

…and the very basic plan originally shown in part 1:

That query plan has an estimated cost of 3.59557. Compare that with the fully-optimized plan cost of 0.0248906:

Producing Partially-Optimised Plans

Using the DMV and query from part 3 we can see that the optimizer used more than 20 different rules to find a fully-optimized plan.

To create the very basic plan, just four core implementation rules are needed (five in SQL Server 2005):

-- Route messages to the client
DBCC TRACEON (3604);
 
-- Ensure the four core implementation rules are available
DBCC RULEON ('GetToScan');
DBCC RULEON ('JNtoNL');
DBCC RULEON ('SelectToFilter');
DBCC RULEON ('GbAggToStrm')
 
-- Required by SQL Server 2005 only
DBCC RULEON ('ReduceGbAgg');

We need to disable the other rules normally considered:

-- Alternative join implementations
DBCC RULEOFF ('JNtoHS');
DBCC RULEOFF ('JNtoSM');
 
-- Index-related transformations
DBCC RULEOFF ('GetIdxToRng');
DBCC RULEOFF ('GetToIdxScan');
DBCC RULEOFF ('SelIdxToRng');
DBCC RULEOFF ('SelToIdxStrategy');
DBCC RULEOFF ('SELonJN');
DBCC RULEOFF ('JNtoIdxLookup');
DBCC RULEOFF ('AppIdxToApp');
DBCC RULEOFF ('SelResToFilter');
DBCC RULEOFF ('WCJNonSELtoIdxLookup');
 
-- Exploration rules
DBCC RULEOFF ('GbAggToHS')
DBCC RULEOFF ('JoinCommute');
DBCC RULEOFF ('GbAggBeforeJoin');
DBCC RULEOFF ('GenLGAgg');
DBCC RULEOFF ('BuildSpool');
DBCC RULEOFF ('ImplRestrRemap');
DBCC RULEOFF ('EnforceSort');
DBCC RULEOFF ('NormalizeGbAgg');

We can now get the basic query plan by executing the AdventureWorks query on its own (or by requesting an estimated plan):

SELECT
    P.ProductNumber, 
    P.ProductID, 
    total_qty = SUM(I.Quantity)
FROM Production.Product AS P
JOIN Production.ProductInventory AS I
    ON I.ProductID = P.ProductID
WHERE
    P.ProductNumber LIKE N'T%'
GROUP BY
    P.ProductID,
    P.ProductNumber
OPTION
    (RECOMPILE);

We have disabled so many important rules that we can no longer run the DMV batch code from part 3. The optimizer is unable to produce any correct plan with the restricted range of rules now available to it, so the following error is returned:

.Net SqlClient Data Provider: Msg 8624, Level 16
Internal Query Processor Error: The query processor could not produce a query plan.

If you want to see the DMV statistics, you will need to clear the plan cache using DBCC FREEPROCCACHE before running the following modified code:

-- 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 AS P
JOIN Production.ProductInventory AS I
    ON I.ProductID = P.ProductID
WHERE
    P.ProductNumber LIKE N'T%'
GROUP BY
    P.ProductID,
    P.ProductNumber
OPTION
    (RECOMPILE);
GO
-- 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 AS S
JOIN sys.dm_exec_query_transformation_stats QTS
    ON QTS.name = S.name
WHERE
    QTS.succeeded <> S.succeeded
OPTION
    (KEEPFIXED PLAN);

You will see results similar to these:

Refer back to part 3 for further details about that output.

A Spool Rule

Let’s explore one more alternative plan by enabling the rule BuildSpool. This is one of the many rules that can introduce a Table Spool operator into the plan to improve estimated cost:

DBCC RULEON ('BuildSpool');

The estimated plan for our test query now looks like this:

It’s still a terrible overall plan, but the introduction of a Lazy Spool on the inner side of the nested loops join has reduced the estimated cost of the plan from 3.59557 to 3.12199 — a worthwhile improvement.

Clean Up

Don’t forget to reset your session by re-enabling the disabled rules:

DBCC RULEON ('JNtoHS');
DBCC RULEON ('JNtoSM');
DBCC RULEON ('GetIdxToRng');
DBCC RULEON ('GetToIdxScan');
DBCC RULEON ('SelIdxToRng');
DBCC RULEON ('SelToIdxStrategy');
DBCC RULEON ('SELonJN');
DBCC RULEON ('JNtoIdxLookup');
DBCC RULEON ('AppIdxToApp');
DBCC RULEON ('SelResToFilter');
DBCC RULEON ('WCJNonSELtoIdxLookup');
DBCC RULEON ('GbAggToHS')
DBCC RULEON ('JoinCommute');
DBCC RULEON ('GbAggBeforeJoin');
DBCC RULEON ('GenLGAgg');
DBCC RULEON ('BuildSpool');
DBCC RULEON ('ImplRestrRemap');
DBCC RULEON ('EnforceSort');
DBCC RULEON ('NormalizeGbAgg');
DBCC RULEON ('ReduceGbAgg');

You can check that all rules are now enabled again by running DBCC SHOWOFFRULES (but see the previous remarks concerning the bugs in SQL Server 2005).

Final thoughts

You might be wondering whether all this has any practical application. For me, a better understanding of optimizer internals enables me to write better queries, and more quickly debug poor plans.

There are also very rare (and advanced) uses where we can generate and capture a ‘customised’ plan for use in a USE PLAN hint.

---

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

---

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