1

Based on Paul White's blog, there are three optimization phases in SQL Server, search 0 (Transaction Processing), search 1 (quick plan) and search 2 (full optimization). Each phase has entry and termination conditions.

My question is, can we force the optimizer to run at one optimization phase?

1
  • 2
    Not really. But there is some info on coercing certain behaviors here and here Nov 3, 2014 at 21:48

1 Answer 1

8

I think this question represents a misunderstanding of the optimisation phases and a willingness to try and override the optimizer ("I know best") when there is really no need to do so, as from my experience the optimizer gets it right most of the time. For the odd occasion it doesn't there are more appropriate tools for experienced developers to work around, eg query rewrites, different indexing strategies, filtered indexes/stats, hints, plan guides, plan forcing. Again from my experience on occasions the optimizer isn't quite getting it right it tends to be because of over-complexity, either in the query or schema design, although there are occasional bugs.

Phases search 0 - 2 are also components of Full Optimisation and not all queries are candidates for this. For example simple queries are much more likely to be optimised as Trivial Plan which is cheaper to run than full cost-based optimisation. Why force full optimisation for simple queries where there is only one path? Additionally optimisation can drop out of these phases if a 'good enough' plan is found.

You can influence some control over the optimizer. Here's a few examples:

  • give the optimizer as much information as possible so it can make good decisions, eg up-to-date statistics, foreign keys, unique indexes, constraints, appropriate datatypes
  • to encourage simple plans you could only write simple queries; build up resultsets eg using temp tables
  • prevent parallel plans - turn off parallelism at server level ( eg with sp_configure 'max degree of parallelism' ), or at query level using OPTION ( MAXDOP 1 ). This isn't necessarily such a bad idea in an OLTP system for example.
  • there is a trace flag for encouraging parallel plans, but not generally recommended

So hopefully you can see it doesn't really make sense to force the optimizer to run in only one of these modes.

Do you have some specific examples in mind where you don't feel the optimiser is doing a good job? Post them here as questions and I'm sure someone will be able to help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.