Hints are an extension of SQL that provide directives to the SQL optimizer for determining the query execution plan that is used. Hints are specified by using embedded pseudo comments within query statements. The optimizer detects these directives or hints and bases its query execution plan accordingly. Optimizer hints allow applications to be optimized under various conditions to the data, query type, and the database. The hints not only provide solutions to performance problems that are occasionally encountered with queries, but shift the control of response times from the system to the user.
Hints are needed if, due to various conditions with the data, user query, and database, the SQL optimizer is not able to choose the best possible execution plan. For example, you might want to force a merge join because you know, unlike the optimizer, that your data is already sorted. Also, sometimes specific predicates in queries can cause performance problems that the optimizer cannot eliminate. The optimizer might be using an index that you know is not optimal. In this case, you might want to force the optimizer to use one that produces faster results.
Hints are available for the following actions:
▪ selecting merge or nested loop join,
▪ using a fixed join order as given in the from list,
▪ selecting internal or external sort,
▪ selecting a particular index,
▪ selecting a table scan over an index scan,
▪ selecting sorting before or after grouping.
You can place hints in SQL statements as static strings, just after a SELECT, UPDATE, or DELETE keyword. Hints are not allowed after the INSERT keyword.
When there is an error in a hint specification, the whole SQL statement fails with an error message.
Enabling and disabling hints
Hints are enabled and disabled by using the SQL.EnableHints parameter, see SQL section. By default, hints are enabled.