Databases Reference
In-Depth Information
will be executed. For example, if you use hints to force a particular join order, the Query
Optimizer will disable rules that reorder joins. Always try to use the least restrictive hint,
as this will retain as much flexibility as possible in your query, and make maintenance
somewhat easier. In addition, hints can not be used to generate an invalid plan or a plan
that the Query Optimizer normally would not consider during query optimization.
Furthermore, a hint that initially does a great job might actively hinder performance at
a later point in time when some conditions change; for example, as a result of schema
updates, service packs, new versions of SQL Server, or even enough data changes. The
hints may prevent the Query Optimizer from modifying the execution plan accord-
ingly, and thus result in degraded performance. It is
your
responsibility to monitor and
maintain your hinted queries to make sure that they continue to perform well after such
system changes or, even better, to remove them if they are no longer needed.
Plan guides
Plan guides can help in this scenario as they allow you to apply hints without changing the text of the
query directly. They separate the hint specification from the query itself, and so are an excellent choice
for applying a hint, or even specifying an entire plan, that can then be easily removed in the future. This
makes them particularly useful when dealing with third-party application code, or if you simply want
to apply hints in a more easily maintainable way. There is a whole section dedicated to exploring plan
guides, at the end of this chapter.
Remember, also, that if you decide to use a hint to change a single section or physical
operator of a plan, then after applying the hint, the Query Optimizer will perform a
completely new optimization. The Query Optimizer will obey your hint during the
optimization process, but it still has the flexibility to change everything else in the
plan, so the end result of your tweaking may be unintended changes to other sections
of the plan.
Finally, note that the Query Optimizer cannot perform miracles. The fact that your query
is not performing as you hoped does not always mean that the Query Optimizer is not
