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.

Inside the Optimizer: Constructing a Plan – Part 4

Summary: More undocumented ways to explore how the query optimiser works.


This post is part of a series: Part1 Part 2 Part3 Part4
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:

Hints

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):

Relational-Tree

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

 

Basic-Plan

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

Fully-Optimised

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

Modified-DMV-Output

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):

Lazy-Spool-Plan

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

© Paul White
email: SQLkiwi@gmail.com

Published Saturday, July 31, 2010 9:41 PM 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

 

Davide Mauri said:

Cooooooooooooooooooooooooooooooooooooooool!

July 31, 2010 7:47 AM
 

Alex Kuznetsov said:

Paul,

I learned a few things, and enjoyed reading this series. Thanks!

I have a suggestion: because much of the traffic is going to read this and other posts via Google search, it usually makes sense to add links to other posts in the series, something like this:

This is the last post of a series. It makes sense to read post in sequence, and the previous ones are:

July 31, 2010 10:12 PM
 

Paul White said:

Thanks, Alex.  Good point: I'll edit your suggestion in now.

Glad you found the series interesting.

Paul

July 31, 2010 10:32 PM
 

Roji said:

Awesome series Paul. I am sure that a lot of this is absolutely "new" to a lot of us. Thanks for sharing!

August 2, 2010 2:54 AM
 

csm said:

Great series!!! Really don't know that we could play with this kind of internals.

Thanks for that!!

August 3, 2010 5:02 AM
 

Paul White said:

Davide, Roji, and 'csm':

Thanks all for your kind comments.  I thoroughly enjoyed writing this series, and may well return to the topic in the near future.

Paul

August 4, 2010 2:21 PM
 

TheSQLGuru said:

I agree with the others - totally cooool yet a bit scary stuff here! :-)

August 8, 2010 11:59 AM
 

Paul White said:

Nice summary, Kevin.  Next time you come across some really weird behaviour on one of your clients' servers - you might find yourself checking DBCC SHOWOFFRULES first ;c)

Glad you enjoyed the series, and seem to have made it all the way to part 4.  It is powerful stuff, but I felt safe sharing it on my blog.  I know all my readers are intelligent and sensible people.

Paul

August 8, 2010 1:38 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
 

Remus Rusanu said:

This whole series is excellent. Congratulations, Paul!

August 19, 2010 3:43 PM
 

Angell said:

Thanks for your wondreful posts ,it's really COOOOOOOOOOOOOL~~~

And I learned much from your Blog ~~~

September 3, 2010 3:48 AM
 

John Alan said:

Thanks for the insight into the inner workings of the optimiser.

January 19, 2011 2:01 PM
 

Paul White: Page Free Space : Inside the Optimiser: Constructing a Plan ??? Part 4 said:

February 26, 2011 8:22 PM
 

Martin said:

Great series of articles. Just to mention that as well as the danger of sub optimal plans this can also cause incorrect plans.

DBCC RULEOFF('SplitSequenceProject');

SELECT *,

      ROW_NUMBER() OVER (PARTITION BY type ORDER BY number)

FROM master..spt_values

WHERE type IN ('T','V')

ORDER BY type

OPTION (RECOMPILE)

DBCC RULEON('SplitSequenceProject');

At least on Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 X86 disabling this rule causes it to act as though the query had been written ROW_NUMBER() OVER (ORDER BY (SELECT 0)). Though it doesn't allow me to then force this plan with the "USE PLAN" hint

March 22, 2012 6:17 AM
 

Paul White said:

Hi Martin,

Thanks, I'm glad you enjoyed the series and have gone on to experiment for yourself!

Not applying the partition as a group by on the segment in your example does indeed expose a wrong-results bug (SplitSequenceProject should be a required rule, not optional there).

SQL Server 2012 fails to compile with that rule disabled giving error 8624, though I was able to reproduce it on 2008 R2 build 2806.

Paul

March 22, 2012 7:55 AM
 

Daidal said:

Thank you Pail. Enjoyed and learned a lot. really amazing.

When I issue DBCC RULEON('GbAggToHS', 'GbAggToStrm'); I get an error that says Query Processor could not produce a query plan. But after running DBCC RULEON('JNtoSM', 'JNtoHS', 'JNtoNL'); the optimizer still generates a plan. By my test it seems we can turn off JNtoSM and JNtoHS but not JNtoNL. Am I missing a point? Can you explain about it?

June 9, 2013 6:15 AM
 

Paul White said:

Hi Daidal,

It's not clear from your comment which query you were executing, or precisely which rules were on and off (there might also be other typos than just misspelling my name).

In general, though, yes JntoNL can be turned off successfully. There are other rules that can generate a nested loops join, and some queries will be happy to find a plan that uses sort merge or hash join instead.

Paul

June 9, 2013 8:56 AM
 

Bikash said:

Hey Paul,

This is just some hidden magic inside SQL which you revealed and showed us how to reveal.

Awesome!!!!

I have one query here. If you could tell the reason that will be great.

You mentioned DBCC SHOWOFFRULES and DBCC SHOWONRULES will work fine in SQL server 2008. But when I am executing it is giving "DBCC execution completed. If DBCC printed error messages, contact your system administrator." Nothing else whereas it should give which rues are enabled and disabled. Why so?

Thanks in advance

Biaksh

December 18, 2013 6:35 AM
 

Paul White said:

Bikash,

You need to have trace flag 3604 enabled for the session: DBCC TRACEON (3604)

Paul

December 18, 2013 9:52 AM
 

Lorrin Ferdinand said:

Nice post!  For folks (like myself) that are less knowledgeable, it would be interesting to know how you can distinguish the difference between a bad query and a bad plan.  Most of the time, when performing troubleshooting, the operating assumption is that the query is deficiient, at what point do you abandon that assumption and start investigating the quality of the plan....

Thanks in advance for any insights!

January 13, 2014 2:31 PM
 

Mike said:

Awesome series. Hash joins are so 2005, time to disable. :)

July 19, 2014 11:14 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement