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

Documented Rule-Affecting Options

It might surprise you to learn that most T-SQL users have written queries that disable one or more of the optimiser rules already.

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

For example, a logical GROUP BY operation (including DISTINCT) can 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 optimiser, and it might consider one or both when optimising 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 optimiser 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 optimiser the choice of sort-merge or hash joins (rules JNtoSM and JNtoHS).  Hinting joins in a query hint also implies 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 optimiser.  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 optimiser uses rules.

The techniques shown below work best in SQL Server 2008, but will also function in SQL Server 2005 (with a number of important caveats).  The specific versions used in writing this entry 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 (Management Studio, for example).

DBCC TRACEON (3604);

We can disable one or more optimiser 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 (with trace flag 3604 on).  The direct effects on the optimiser only apply to the current session, but the (sub-optimal) plans produced will be cached as normal – another great reason to only play with this stuff on a personal test system!

To reset to normal operation, 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-optimised 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 optimiser, and the new DBCC commands to selectively disable them to see the effect on the final plan.

Here’s our sample AdventureWorks query’s logical relational tree representation again (click to enlarge):

That query plan has an estimated cost of 3.59557 – compare that to the fully-optimised plan’s cost of 0.0248906:

Producing Partially-Optimised Plans

Using the DMV and query from part 3 we saw that the optimiser used more than twenty different rules in finding a fully-optimised 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 P
JOIN    Production.ProductInventory 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 optimiser is unable to produce any correct plan with the restricted(adj. 受限制的；保密的) range of rules now available to it, so the following error is returned:

.Net SqlClient Data Provider: Msg 8624, Level 16, State 1, Line 1 
Internal Query Processor Error: The query processor could not produce a query plan. 
For more information, contact Customer Support Services.

If you want to see the DMV statistics, you’ll 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 P
JOIN    Production.ProductInventory 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 S
JOIN    sys.dm_exec_query_transformation_stats QTS
        ON QTS.name = S.name
WHERE   QTS.succeeded != S.succeeded
OPTION  (KEEPFIXED PLAN);

You’ll 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 efficiency:

DBCC RULEON ('BuildSpool');

The estimated plan for our test query now looks like this (again, click to enlarge):

It’s still a terrible overall plan of course, 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 my 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 optimiser internals enables me to write better queries, and more quickly debug poor plans.

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

That’s all for this post, as always your comments and feedback are welcome below.  If you’re a twitter user, you’ll find me there as @SQL_Kiwi