Database Reference
In-Depth Information
One of the key elements that you need to remember is that Query Optimizer is not looking for the
best execution
plan
that exists for the query. Query optimization is a complex and expensive process, and it is often impossible to
evaluate all possible execution strategies.
For example, inner joins are commutative, and thus the result of
(A join B)
is equal to result of
(B join A)
.
Therefore, there are two possible ways that SQL Server can perform a two-table join; six ways that it can do three-table
joins and
N!
, which is
(N * (N - 1) * (N - 2) * ..)
combinations for an N-table join. For a ten-table join, the
number of possible combinations is 3,628,800, which is impossible to evaluate in a reasonable time period. Moreover,
there are multiple physical join operators, which increase that number even further.
Optimization time is another important factor. For example, it is impractical to spend an extra ten seconds on
optimization only to find an execution plan that saves just a fraction of a second during execution.
■
Important
the goal of Query Optimization is to find a
good enough
execution plan,
quickly enough
.
SQL is declarative language in which you should focus on what
needs to be done
rather than
how
to achieve it
.
As a general rule, you should not expect that the way you wrote a query should affect the execution plan. SQL Server
applies various heuristics that transform the query internally, removing contradicting parts, and changing join orders
and performing other refactoring steps.
As with other general rules, they are correct only up to a degree. It is often possible to improve the performance
of a query by refactoring and simplifying it, removing correlated subqueries, or splitting a complex query down into
a few simple ones. As you know, cardinality estimation errors quickly progress and grow through the execution plan,
which can lead to suboptimal performance, especially with very complex queries.
Moreover, you should not expect an execution plan for a particular query always to be the same and to rely on it as
such. Query Optimizer algorithms change with every version of SQL Server and even with service pack releases. Even when
this is not the case, statistics and data distribution changes lead to recompilation and potentially different execution plans.
■
in some cases, you can use query and table hints and/or plan guides to control the execution plan shape.
We will discuss table hints in more detail later in this chapter and plan guides in the following chapter.
Note
In the end, having the right indexes and efficient database schema is the best way to achieve predictability and
good system performance. They simplify execution plans and make queries more efficient.
Query Optimization
The query optimization process consists of the multiple phases, as shown in Figure
25-2
.
Figure 25-2.
Query optimization phases
