Database Reference
In-Depth Information
Figure 25-18.
Workload distribution before and after a statistics update
■
Note
We will discuss best practices and other parallelism-related questions in Chapter 27, “System troubleshooting.”
Query and Table Hints
Query Optimizer usually does a good job of generating decent execution plans. However, in some cases, you can
decide to fine-tune the shape of the execution plan with query and table hints. For example, query and table hints
allow you to force Query Optimizer to choose specific indexes or join types for the query.
Query hints are a great, but very dangerous, tool. While on the one hand they can help you improve the quality
of execution plans, they could significantly decrease the performance of the system when applied incorrectly on the
other. You should have a very good understanding of how SQL Server works and know your system and data if you
decide to use them.
The supportability of the system is another very important factor. You should document the cases when hints are
used and periodically re-evaluate if they are still required. The amount of data and data distribution changes can lead
to situations where plans forced by the hints become suboptimal. For example, consider the situation when a hint
forces Query Optimizer to use a nested loop join. This join type will work inefficiently as the amount of data and size
of inputs grows.
Forcing Query Optimizer to use a specific index is another example. The choice of index can become inefficient
in the case of data selectivity changes, and it would prevent Query Optimizer from using other indexes, which were
created later. Moreover, the code would be broken and queries would error out if you ever dropped or renamed the
index referenced by the hint.
As a general rule, you should only use hints as a last resort. If you do, make sure that the statistics are up to date
and that the query cannot be optimized, simplified, or refactored before applying them.
In the case of parameter sniffing, it is usually better to use the
OPTIMIZE FOR
hint or statement-level recompile
rather than force specific index usage with an index hint. We will discuss these approaches in greater depth in the
next chapter.
INDEX Query Hint
INDEX
is, perhaps, one of the most commonly used Query Hints, which forces Query Optimizer to use a specific index
for data access. It requires you to specify either the name or id of the index as a parameter. In most cases, the name
of the index is the better choice for supportability reasons. There are two exceptions, however, where index id is the
better option, forcing the use of a clustered index or heap table scan. You can consider using 1 and 0 respectively as an
id in those cases.
