SQL Server Table Hints

Index hints (a form of a table hint) are used to specify which index/s you want used when a query runs. When you specify one or more index, the Query Optimizer will use them when creating the execution plan for your query.

The syntax for a table hint is:

SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])

Here's an example:

SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))

While you can use an index ID instead of the index name, this is not a good idea. This is because index IDs can change if you drop and recreate the indexes differently than when you first created them.

In most cases, the SQL Server Query Optimizer will correctly evaluate a query and run it as optimally as possible. But on occasion the Query Optimizer will fail, producing a less than optimal execution plan, and query performance will suffer because of it. When you identify such a query, you can override the Query Optimizer using what is called an optimizer hint. Optimizer hints can be divided into five different categories:
Table Hints: Used to force index selection.
Join Hints: Used to specify the type of JOIN strategy used.
Query Hints: Hints used to affect GROUP BY and UNION functionality.
Lock Hints : Used to help avoid undesirable locking.
View Hints: Used to specify indexes in indexed views.
Other Hints: Misc. hints.

In essence, what a hint does is override what the Query Optimizer recommends. If the Query Optimizer is wrong, then using a hint is very beneficial. But if the hint you specify is wrong, or not ideal, it will continue to be wrong until you change it, hurting performance until it is corrected.

No comments:

Post a Comment