Databases Reference
In-Depth Information
giving you the best possible execution plan. If the operation you are performing is simply
expensive and resource intensive, then it's possible that no amount of tuning or hinting
will help you achieve the performance you'd like.
Types of Hints
SQL Server provides a wide range of hints which can be classified as follows:
•
query hints
tell the optimizer to apply "this hint" throughout the entire query and are
specified using the
OPTION
clause, which is included at the end of the query
•
join hints
apply to a specific join in a query, and can be specified by using the
ANSI
-
style join hints
•
table hints
apply to a single table and are usually included using the
WITH
keyword on
the
FROM
clause.
Another useful classification is dividing hints into physical operator and goal oriented
hints. Physical operator hints, as the name suggests, request the use of a specific physical
operator, join order or aggregation placement. On the other hand, a goal oriented hint
does not specify how to build the plan, but instead specifies a goal to achieve, leaving the
Query Optimizer to find the best physical operators to achieve that goal. Goal oriented
hints are usually safer and require less knowledge about the internal workings of the
Query Optimizer. Examples of goal oriented hints include the
OPTIMIZER
FOR
or
FAST
N
hints. Almost all the remaining hints covered in this chapter are physical hints.
Locking hints do not affect plan selection, so they will not be covered here. Plan guides,
which allow you to apply a hint to a query without changing the code in your application,
and the
USE
PLAN
query hint, which allows you to force the Query Optimizer to use a
specified execution plan for a query, are covered separately, later in the chapter.
