SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 10

Query Optimizer
The Query Optimizer is the most prized possession of the SQL Server team and one of the most
complex and secretive parts of the product. Fortunately, it's only the low-level algorithms and source
code that are so well protected (even within Microsoft), and research and observation can reveal
how the Optimizer works.

It is what's known as a “cost-based” optimizer, which means that it evaluates multiple ways to
execute a query and then picks the method that it deems will have the lowest cost to execute. This
“method” of executing is implemented as a query plan and is the output from the Query Optimizer.

Based on that description, you would be forgiven for thinking that the Optimizer's job is to i nd the
best query plan because that would seem like an obvious assumption. Its actual job, however, is to
i nd a good plan in a reasonable amount of time, rather than the best plan. The optimizer's goal is
most commonly described as i nding the most efi cient plan.

If the Optimizer tried to i nd the “best” plan every time, it might take longer to i nd the plan than it
would to just execute a slower plan (some built-in heuristics actually ensure that it never takes
longer to i nd a good plan than it does to just i nd a plan and execute it).

As well as being cost based, the Optimizer also performs multi-stage optimization, increasing
the number of decisions available to i nd a good plan at each stage. When a good plan is found,
optimization stops at that stage.

The i rst stage is known as pre-optimization , and queries drop out of the process at this stage
when the statement is simple enough that there can only be one optimal plan, removing the need for
additional costing. Basic queries with no joins are regarded as “simple,” and plans produced as such
have zero cost (because they haven't been costed) and are referred to as trivial plans.

The next stage is where optimization actually begins, and it consists of three search phases:

➤

Phase 0 — During this phase the optimizer looks at nested loop joins and won't consider
parallel operators (parallel means executing across multiple processors and is covered in
Chapter 5).
The optimizer will stop here if the cost of the plan it has found is < 0.2. A plan generated at
this phase is known as a transaction processing , or TP , plan.

➤

Phase 1 — Phase 1 uses a subset of the possible optimization rules and looks for common
patterns for which it already has a plan.
The optimizer will stop here if the cost of the plan it has found is < 1.0. Plans generated in
this phase are called quick plans.

➤

Phase 2 — This i nal phase is where the optimizer pulls out all the stops and is able to use
all of its optimization rules. It also looks at parallelism and indexed views (if you're running
Enterprise Edition).
Completion of Phase 2 is a balance between the cost of the plan found versus the time spent
optimizing. Plans created in this phase have an optimization level of “Full.”