Databases Reference
In-Depth Information
we are going against the declarative property of the SQL language and, instead, giving
direct instructions to the Query Optimizer. Overriding the Query Optimizer is a risky
business; hints need to be used with caution, and only as a last resort when no other
option is available to produce a viable plan.
With this warning in mind, this chapter will review some of the hints that SQL Server
provides, should the need arise, as well as how and when they might be used. It does
not
attempt to provide comprehensive coverage; indeed, we'll focus only on those hints I've
most often seen provide positive performance benefits in certain circumstances, and we'll
look at those in a few pages. Some other query hints, like
OPTIMIZE
FOR
,
OPTIMIZE
FOR
UNKNOWN
and
RECOMPILE
have already been covered in the Parameter Sniffing section of
Chapter 6, and will not be touched upon again in this chapter.
Before You Reach for a Hint…
Hints are a powerful means by which we can cause our decisions to overrule those of the
Query Optimizer. However, we should only do so with extreme caution, because hints
restrict the choices available to the Query Optimizer, will make your code less flexible,
and will require additional maintenance. A hint should only be employed once you're
certain that you have no alternative options. As a minimum, before you reach for a hint,
you should explore the potential issues below.
•
Check for system problems -
You need to make sure that your performance problem
is not linked to other system-related issues, such as blocking, or bottlenecks in server
resources such as I/O, memory, or CPU.
•
Check for cardinality estimation errors -
The Query Optimizer often misses the
correct plan because of cardinality estimation errors. Cardinality estimation errors
can be detected using the
SET
STATISTICS
PROFILE
ON
statement, and can often be
fixed using solutions like updating statistics, using a bigger sample for your statistics
(or scanning the entire table), using computed columns, or filtered statistics, etc. There
