Databases Reference
In-Depth Information
The reality is that query optimizers are highly complex pieces of software which, even
after more than 30 years of research, still face technical challenges, some of which will
be mentioned in the next section. As a result, there may be cases when, even after you've
provided the Query Optimizer with all the information it needs, and there doesn't seem
to be any apparent problem, you are still not getting an efficient plan; in these cases you
may want to resort to hints. However, since hints let you to override the operations of the
Query Optimizer, they need to be used with caution, and only as a last resort when no
other option is available. Hints are instructions that you can send to the Query Optimizer
to influence a particular area of an execution plan. For example, you can use hints to
direct the Query Optimizer to use a particular index or a specific join algorithm. You can
even ask the Query Optimizer to use a specific execution plan, provided that you specify
one in XML format. Hints, and cases where you may need to use them, will be covered in
Chapter 7
,
Hints
.
Ongoing Query Optimizer Challenges
Query optimization is an inherently complex problem, not only in SQL Server, but in
any other relational database system. Despite the fact that query optimization research
dates back to the early seventies, challenges in some fundamental areas are still being
addressed today. The first major impediment to a query optimizer finding an optimal
plan is the fact that, for many queries, it is just not possible to explore the entire search
space. An effect known as combinatorial explosion makes this exhaustive enumeration
impossible, as the number of possible plans grows very rapidly depending on the number
of tables joined in the query. To make the search a manageable process, heuristics are
used to limit the search space (these will be touched upon again in
Chapter 5
,
The
Optimization Process
). However, if a query optimizer is not able to explore the entire
search space, there is no way to prove that you can get an absolutely optimal plan, or
even that the best plan is among the candidates being considered. As a result, it is clearly
extremely important that the set of plans which a query optimizer considers contains
plans with low costs.
