More things you shouldn't do in SQL Server: Disabling Optimisation Phases

PLEASE DON'T DO THIS

In my last post I covered optimisation phases and we can view optimiser information by using Trace Flag 8675. The last post looked at the search(0) and search(1) phases and we saw how the optimiser improved the cost of the query by using both of the phases with the search(1) phase resulting in the better plan in terms of cost.

I mentioned in that post that the optimiser has another phase it can use, no surprise it's called search(2) also known as the full optimisation phase (yes that's the full optimisation phase of the full optimisation stage, still with me? Good!).

The full optimisation phase I suppose would normally be reserved for those beastly queries that the optimiser really has to get to work on, looking at you Entity Framework but we can force optimiser behaviour by using an undocumented trace flag 8677 which entirely skips the search(1) phase. 

Now it has to be said it's undocumented for a reason, the reason is that it's really not a good idea to do this. In fact enabling this trace flag is such a bad idea that it will probably cause no end of issues with query performance...so let's do it, but before we do let me add yet again that please don't do this! Disabling optimisation features is a really bad idea, just like we did in this post - the purpose for this demo is just to show that we can, and how dangerous it can get.

Right, on with the show...

DBCC TRACEON(3604, -1);

DBCC TRACEON(8675, -1); 

DBCC TRACEON(8677, -1); 

As in the last post I've enabled trace flags 3604 and 8675 and this time I've also enabled trace flag 8677. I'm going to run the following query as it won't be able to use the search(0) phase as it's only using two tables and the search(0) phase requires at least three tables in the query:

SELECT * FROM Person.Person per
INNER JOIN Sales.Customer cus ON cus.PersonID = per.BusinessEntityID
OPTION (RECOMPILE);

The last time we ran the query (or we can look at the estimated execution plan) the output was the following:

End of simplification, time: 0.002 net: 0.002 total: 0 net: 0.002

end exploration, tasks: 57 no total cost time: 0.001 net: 0.001 total: 0 net: 0.004

end search(1),  cost: 3.99696 tasks: 211 time: 0.002 net: 0.002 total: 0 net: 0.006

End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.006

End of query plan compilation, time: 0 net: 0 total: 0 net: 0.007

This time though because we've disabled the search(1) phase we get this instead:

End of simplification, time: 0 net: 0 total: 0 net: 0

end exploration, tasks: 57 no total cost time: 0 net: 0 total: 0 net: 0.001

end search(2),  cost: 3.99696 tasks: 211 time: 0.001 net: 0.001 total: 0 net: 0.002

End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.002

End of query plan compilation, time: 0 net: 0 total: 0 net: 0.003

We can see the trace flag has worked, search(1) has been skipped and instead it has used the search(2) optimisation phase and apart from a very slight difference in compilation time the result is exactly the same which is no surprise as the search(2) phase contains all the transformation rules that can be used by the optimiser.

Let's try the query that is using three tables: 

SELECT * FROM Person.Person per

INNER JOIN Sales.Customer cus ON cus.PersonID = per.BusinessEntityID

INNER JOIN Sales.SalesOrderHeader soh ON soh.CustomerID = cus.CustomerID 

OPTION (RECOMPILE);

End of simplification, time: 0 net: 0 total: 0 net: 0

end exploration, tasks: 71 no total cost time: 0.001 net: 0.001 total: 0 net: 0.001

end search(0),  cost: 14.5642 tasks: 386 time: 0.001 net: 0.001 total: 0 net: 0.003

end exploration, tasks: 582 Cost = 14.5642 time: 0 net: 0 total: 0 net: 0.003

end search(2),  cost: 6.57863 tasks: 1059 time: 0.002 net: 0.002 total: 0 net: 0.006

End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.006

End of query plan compilation, time: 0 net: 0 total: 0 net: 0.007

Again we see the same as the previous post, the optimiser has used two phases and we can see the cost improvement gained between the search(0) and this time the search(2) phase. But at this point I'm wondering that if we can skip search(1), can we disable the others as well?

Yes we can (insert evil laugh here).

Trace flag 8677 also some, 8670 to skip search(2) and 8750 to skip search(0). Again, don't do this...ever!! Let's start with 8670:

DBCC TRACEON(8670, -1); 

As expected, because SQL won't even give me a warning, probably because it thinks nobody in their right mind would do this, the search(2) has been skipped which has resulted in a query that has a higher cost than if it could have used either search(1) or search(2):

End of simplification, time: 0.001 net: 0.001 total: 0 net: 0.001

end exploration, tasks: 71 no total cost time: 0.001 net: 0.001 total: 0 net: 0.002

end search(0),  cost: 14.5642 tasks: 386 time: 0.001 net: 0.001 total: 0 net: 0.003

End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.004

End of query plan compilation, time: 0.001 net: 0.001 total: 0 net: 0.006

So what happens if we disable search(0) I can hear you asking?? 

DBCC TRACEON(8750, -1); 

And when I try to run the query again:

Msg 8624, Level 16, State 1, Line 8

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

Well it seems we've broken it (you notice I've used "we've" there) - query optimisation has essentially been disabled, as we say in Yorkshire, it's well and truly knackered.

If anything it's shown how easy it can be to enable a couple of undocumented things and cause no end of problems and a SQL Server that isn't working any more. Interestingly enough even if I remove the OPTION (RECOMPILE) which should retrieve the query from cache I still get the error. In order to set things right in the universe I'll quickly disable those trace flags again:

DBCC TRACEOFF(8677, -1); 

DBCC TRACEOFF(8670, -1); 

DBCC TRACEOFF(8750, -1);

DBCC TRACEOFF(8675, -1); 

DBCC TRACEOFF(3604, -1);