In most cases, the query optimizer chooses the fastest execution plan. However, there are some special situations in which the optimizer, for some particular reasons, cannot find the optimal solution. In such cases, you should use optimization hints to force it to use a particular execution plan that could perform better.
What are optimization hints
Optimization hints are optional parts in a SELECT statement that instruct the query optimizer to execute one specific behavior. In other words, by using optimization hints, you do not allow the query optimizer to search and find the way to execute a query because you tell it exactly what to do.
Why use optimization hints
You should use optimization hints only temporarily and for testing. In other words, avoid using them as a permanent part of a query. There are two reasons for this statement. First, if you force the optimizer to use a particular index and later define an index that results in better performance of the query, the query and the application to which it belongs cannot benefit from the new index. Second, Microsoft continuously strives to make the query optimizer better. If you bind a query to a specific execution plan, the query cannot benefit from new and improved features in the subsequent versions of SQL Server.
There are two reasons why the optimizer sometimes does not choose the fastest execution plan:
- The query optimizer is not perfect
- The system does not provide the optimizer with the appropriate information
NOTE
Optimization hints can help you only if the execution plan chosen by the optimizer is not optimal. If the system does not provide the optimizer with the appropriate information, use the AUTO_CREATE_STA TISTICS and AUTO_UPDATE_STATISTICS database options to create or modify existing statistics.
Types of optimization hints
The Database Engine supports the following types of optimization hints:
- Table hints
- Join hints
- Query hints
- Paln guides
Leave a Reply