Things you shouldn't really do in SQL Server: Disabling Join Types Globally

A while ago I presented a session which covered transformation rules that are used by the query optimiser to produce our execution plans. I'm not feeling in the mood for relational algebra this morning so instead I'll introduce a command that can cause mayhem on an instance SQL Server: DBCC RULEOFF.

DBCC RULEOFF is an undocumented command, that alone makes me want to use it but in order to cover one's backside please don't do any of the following in any environment apart from a disposable sandbox that only you use for weird and wonderful experiments in SQL Server, because we are going to break it...

Let's take a simple query with a join:

SELECT e.BusinessEntityID, e.JobTitle, p.FirstName
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID;

If I look at the estimated execution plan I can see that the optimiser has selected a nested loop join:

Now I could use the QUERYRULEOFF hint to disable that join for this particular query and for this particular join type I'd need two different hints to disable nested loop operators for the join:

OPTION (QUERYRULEOFF JNtoNL, QUERYRULEOFF JNtoIdxLookup);

This will force the optimiser to use a different join type and the estimated plan this time shows a hash match join but for this article we're not looking at QUERYRULEOFF, we're looking at DBCC RULEOFF which will still do the same thing only it's not for one query, it's for the entire instance.

MWAHAHAHAHAHAHA

That's right, running DBCC RULEOFF('JNtoNL') will essentially switch off the nested loop join for every single query running on my instance, well, sort of. After running the command I can check what rules are off on my instance by first running DBCC TRACEON(3604) to redirect DBCC output to my messages tab and then run DBCC SHOWOFFRULES:

Rules that are off globally:

JNtoNL

That's just one of the rules though, so just as when I used the query hint with without nested loop joins I'll also run DBCC RULEOFF('JNtoIdxLookup') to disable the other transformation rule that the optimiser can use for a nested loop join and verify again with DBCC SHOWOFFRULES. Now that they're both disabled the estimated plan looks a bit different and is using a hash match operator instead.

A different logical join type will not be affected. If I change the INNER JOIN to a LEFT JOIN and check the execution plan I will see a nested loop operator again.

SELECT e.BusinessEntityID, e.JobTitle, p.FirstName

FROM HumanResources.Employee e

LEFT JOIN Person.Person p

ON e.BusinessEntityID = p.BusinessEntityID;

This is how transformation rules work, essentially they are a substitution for a logical operation (INNER JOIN, LEFT JOIN etc) for a physical operation, that is the operator we see in the execution plan. So far we've only disabled the following rules JNtoNL and JNtoIdxLookup and they only affect the INNER JOIN logical operations. 

For left joins we'll need to switch off the corresponding transformation rules (LOJNtoNL and LeftSideJNtoIdxLookup) and when done we can see the same effect on the estimated execution plan as before where the optimiser has used a hash match join.

Now whilst I can make a case of disabling rules at a query level to check execution plans using different join types (or you could use join hints) I can't really make the same case for disabling them at an instance level. But this post is titled "Things you shouldn't really do..." so let's do something completely reckless.

If I run the following query I can see every transformation rule that contains 'JN' as in JOIN and I've also included the command to disable that rule:

SELECT [name], 'DBCC RULEOFF (''' + [name] + ''');' AS [DontDoIt!!!]

FROM sys.dm_exec_query_transformation_stats

WHERE [name] LIKE '%JN%';

For no other reason but for widespread chaos I can now disable every transformation rule that substitute joins by running each all of the commands returned by my query. Now things are so bad that if I try to run my query I get the following error:

Msg 8624, Level 16, State 1, Line 5

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I think that means it's broken, at least if I want to join any tables anyway!

So please, please, please don't do this. There's literally no reason to do so on any SQL system of any kind. I will put everything back by running the following so I can generate the commands to switch on all rules once again and verify with DBCC SHOWOFFRULES that nothing has remained disabled. 

SELECT [name], 'DBCC RULEON (''' + [name] + ''');' AS [DoIt!!!]

FROM sys.dm_exec_query_transformation_stats

WHERE [name] LIKE '%JN%';

Another reason why I stay well away from relational algebra.