Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Disabling SQL Server Optimizer Rules with QUERYRULEOFF


By:   |   Read Comments (6)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

You are tuning a SQL Server query and for some reason you think that it will do better if you disable an optimizer rule. In this tip I will show you the undocumented QUERYRULEOFF hint.

Solution

Performance query tuning is a complex task because it involves many different aspects like proper indexing, good statistics maintenance and even data partitioning. It depends on your desired level of optimization. In my past tip Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON I explained the usage of the undocumented query hint QUERYTRACEON that allows us to enable a trace flag for a query instead of enabling the flag at the instance level. But the possibilities for tuning that SQL Server doesnít end here. There is another undocumented hint, QUERYRULEOFF that lets us disable an optimizer rule for a specific query.

What Are the Optimizer Rules?

We all know that every time SQL Server executes a query it builds an execution plan that translates the logical operations like joins and predicates into physical operations that are implemented in the SQL Server source code. That conversion is based on certain rules known as the Optimizer Rules. They define for example how to perform an INNER JOIN. When we write a simple select statement with an inner join, the query optimizer chooses based on statistics, indexes and enabled rules if the join is executed as a Merge Join, Nested Loop or a Hash Join and also if the join can use the commutative property of joins. Mathematically A join B is equal to B join A, but the computational cost generally is not the same.

Getting the List of Available Rules

To obtain the list of rules of your version of SQL Server we must use the undocumented DBCC commands SHOWONRULES and SHOWOFFRULES. Those commands display the enabled and disabled rules for the whole instance respectively. As you may guess, the number of rules varies amongst versions.

USE master
GO

DBCC TRACEON(3604)
GO

DBCC SHOWONRULES
GO

DBCC SHOWOFFRULES
GO

The names of the rules are easy to interpret in the most cases. On the next table I will describe some of the rule names related to joins.

Rule Name Description

JNtoNL

Join to Nested Loop

JNtoHS

Join to Hash

JNtoSM

Join to Sort Merge

LOJNtoNL

Left Outer Join to Nested Loop

LSJNtoHS

Left Semi Join to Hash

LASJNtoSM

Left Anti Semi Join to Sort Merge

Sample

For the purposes of this tip I am using the AdventureWorks2012 database which you can download for free from CodePlex at this link http://msftdbprodsamples.codeplex.com/releases/view/55330.

Letís take a look at the following query and its execution plan.

USE AdventureWorks2012
GO
 
 SELECT  c.CustomerID ,
   c.PersonID ,
   c.StoreID ,
   c.TerritoryID ,
   c.AccountNumber ,
   c.rowguid ,
   c.ModifiedDate
 FROM Sales.SalesOrderHeader OH 
   INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID
 WHERE   OH.ShipMethodID = 1
GO

As you can see on the next image, the query optimizer decided that the best approach to execute the query is to use a Hash Join and compute the join order.

Query Optimizer's default Execution Plan

Now we are going to disable the commutatively (i.e. order) of joins by setting off the rule JoinCommute.

USE AdventureWorks2012
GO
 
SELECT  c.CustomerID ,
   c.PersonID ,
   c.StoreID ,
   c.TerritoryID ,
   c.AccountNumber ,
   c.rowguid ,
   c.ModifiedDate
FROM Sales.SalesOrderHeader OH 
   INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID
WHERE OH.ShipMethodID = 1
OPTION( QUERYRULEOFF JoinCommute )
GO

The following image shows that the join order has changed as well as the missing index impact.

Execution Plan with JoinCommute Rule Disabled

But in order to compare which plan is better, we must compare the Estimated Subtree Cost for each query. If you do so, you will notice that the plan without the JoinCommute has a lower cost like the following image shows.

Estimated Subtree Cost Comparison for Both Queries.

Letís see what happens with the execution plan if we disable the Join to Hash Join rule.

USE AdventureWorks2012
GO
 
SELECT  c.CustomerID ,
   c.PersonID ,
   c.StoreID ,
   c.TerritoryID ,
   c.AccountNumber ,
   c.rowguid ,
   c.ModifiedDate
FROM Sales.SalesOrderHeader OH 
   INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID
WHERE OH.ShipMethodID = 1
OPTION( QUERYRULEOFF JNtoHS )
GO

On the next image we can observe that the query optimizer has decided to use a Merge Join.

Execution Plan with JNtoHS Rule Disabled.

But if we look at the Estimated Subtree Cost we can see that it was a bad choice to disable that rule.

Estimated Subtree Cost with JNtoHS Rule Disabled.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 09, 2016 - 11:52:12 AM - Bill Back To Top

 Great article!  This is truely a deep-dive subject, and you have provided a clear and concise example.  Thanks!

 


Wednesday, March 09, 2016 - 9:14:53 AM - Sherry Back To Top

I'm pretty new to query tuning but want to learn more.  My eye catches on the "Missing Index" line in the execution plan.  Doesn't that mean you should add an index?  Or is this a ridiculous question and I just need to start with the basic tutorial?

 


Saturday, February 27, 2016 - 11:24:28 PM - Daniel Farina Back To Top

 

Hi Oleksii!!

First and foremost thanks for reading! Yes you can... Just need to run the following DBCC command...

 

DBCC RULEOFF('JNtoNL')

 

But use it with caution...

 

Best Regards!


Saturday, February 27, 2016 - 11:22:10 PM - Daniel Farina Back To Top

Hi Jeremy! Thanks to you and the whole community that mades MSSQLTips!!!For fifty more to come!!!


Thursday, February 25, 2016 - 8:17:02 AM - Jeremy Kadlec Back To Top

Daniel,

Good morning and congrats on your 50th tip!  This is an awesome accomplishment and the community truly values all of your contributions.

Keep your tips coming and keep helping the global SQL Server Community!

Congrats!

Thank you,
Jeremy Kadlec
MSSQLTips.com Co-Founder


Thursday, February 25, 2016 - 4:31:40 AM - Oleksii Back To Top

 Hi!

Do you know if it is possible or not to disable JNtoNL rule for the whole instance?

Thanks!

 


Learn more about SQL Server tools