Databases Reference
In-Depth Information
Finally, bear in mind that, when the
USE
PLAN
hint is used directly in a query, an invalid
plan will make the query fail. However, when the
USE
PLAN
hint is used in a plan guide,
an invalid plan will simply compile the query without the requested hint, as mentioned in
the previous section.
Summary
The Query Optimizer typically selects a good execution plan for your queries, but there
may still be cases when you are not getting good performance from a selected plan, even
after extensive troubleshooting. Although hints can be used to improve the performance
of a query in these cases by directly taking control of the execution plan selection, they
should always be used with caution, and only as a last resort. You should also be aware
that code using hints will require additional maintenance, and is significantly less flexible
to changes in your database, application or software upgrades.
This chapter explained how to use hints to force join algorithms, join order, aggregations,
indexes for both scan or seek operations, and the use of indexed views, among other
behaviors. We also examined the use of plan guides to implement hints without changing
the code of your (or third-party) applications, and the ability of the
USE
PLAN
hint to
specify an entire XML plan as the target of the optimization.
Finally, my hope is that the chapters of this topic have provided you with the knowl-
edge needed to write better queries, and to give the Query Optimizer the information
it needs to produce efficient execution plans. At the same time, I hope you've seen more
about how to get the information you need to diagnose and troubleshoot the cases when
(despite your best efforts) you are
not
getting a good plan. In addition, having seen how
the Query Optimizer works, and some of the limitations this complex piece of software
still faces today, you can be better prepared to decide when and how hints can be used to
improve the performance of your queries.
