Databases Reference
In-Depth Information
might be cases where the cardinality estimation errors are caused by the use of features
in which statistics are not supported at all, such as table variables or multi-statement
table-valued functions. In these particular instances you may consider using standard
or temporary tables if you are not getting an efficient plan. Statistics and cardinality
estimation errors are covered in more detail in
Chapter 3, Statistics and Cost Estimation
.
•
Additional troubleshooting
- You may need to perform additional troubleshooting
before considering the use of hints. One of the obvious choices for improving the
performance of your queries is providing the Query Optimizer with the right indexes.
How to make sure that your indexes are selected by the Query Optimizer is covered
in
Chapter 4, Index Selection
. You might also consider some other, less obvious trouble-
shooting procedures, like partitioning your query into steps or smaller pieces and
storing any intermediate results in temporary tables. Temporary tables can give you
the benefit of additional statistics which can help the Query Optimizer to produce
more efficient plans. You can use this method just as a troubleshooting procedure, for
example, to find out which part of the original query is expensive, so you can focus
on it. Alternatively, you can keep it as the final version of your query if these changes
alone give you better performance.
As discussed in this topic's introduction, query optimizers have improved radically after
more than 30 years of research, but still face some technical challenges. The SQL Server
Query Optimizer will give you an efficient execution plan for most of your queries, but
will be increasingly challenged as the complexity of the query grows with more tables
joined, plus the use of aggregations, and other SQL features.
If, after investigating the troubleshooting options and recommendations described above
and throughout this topic, you still ind that the Query Optimizer is not inding a good
execution plan for your query, then you may need to consider using hints to direct the
Query Optimizer toward what you feel is the optimal execution path.
Always remember that, by applying a hint, you effectively disable some of the available
transformation rules to which the Query Optimizer usually has access, and so restrict the
available search space. Only transformation rules that help to achieve the requested plan
