Using the INDEX and FORCESEEK Query Hints in Plan Guides

You can specify INDEX and FORCESEEK table hints as query hints. When specified as query hints, these hints behave the same way as an inline table or view hint.

The INDEX hint forces the query optimizer to use only the specified indexes to access the data in the referenced table or view in the query. The FORCESEEK hint forces the optimizer to use only an index seek operation to access the data in the referenced table or view. These hints can be used in the OPTION clause of a plan guide to influence the optimization of a query. When a query matches a plan guide, the OPTION clause specified in the plan guide is added to the query before it compiles and optimizes. For more information about plan guides, see Understanding Plan Guides.

Warning

Plan guides that misuse query hints can cause compilation, execution, or performance problems. Plan guides should be used only by experienced developers and database administrators.

When specified as a query hint, the INDEX and FORCESEEK table hints are valid for the following objects:

• Tables

• Views

• Indexed views

• Common table expressions (The hint must be specified in the SELECT statement whose result set populates the common table expression.)

• Dynamic management views

• Named subqueries

Table hints cannot be specified for table-valued functions, table variables, or OPENROWSET statements.

To specify an index hint for an indexed view, the NOEXPAND hint must also be specified in the OPTION clause, otherwise the index hint is ignored. For more information, see Resolving Indexes on Views.

For information about the syntax used to specify the INDEX and FORCESEEK hints as query hints, see Query Hints (Transact-SQL).

Best Practices

We recommend the following best practices:

• Use the INDEX and FORCESEEK hints as query hints only in the context of a plan guide or in ad-hoc queries when testing plan guide statements. For all other ad-hoc queries, specify these hints as table hints.

• Before using the FORCESEEK hint, make sure that statistics on the database are current and accurate.

  Up-to-date statistics allow the optimizer to accurately assess the cost of different query plans and choose a high-quality plan. Therefore, we recommend setting the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to ON (the default) for every user database. Alternatively, you can manually update statistics on a table or view by using the UPDATE STATISTICS statement.

• Do not unnecessarily use the INDEX hint in combination with FORCESEEK. That is, if FORCESEEK alone produces an adequate plan, also using the INDEX hint may excessively limit the optimizer's choices. Furthermore, an INDEX hint will cause your query to fail if you change the physical schema of your table to eliminate the index specified in the hint. By contrast, as long as at least one usable index exists on the table on which the FORCESEEK hint is applied, the query will compile even as you change your index structures.

• Do not use the INDEX hint INDEX (0) with the FORCESEEK hint. INDEX (0) forces a scan of the base table. When used with FORCESEEK, no plan is found, and error 8622 is returned.

• Do not use the USE PLAN query hint with the FORCESEEK hint. If you do, the FORCESEEK hint is ignored.

Using INDEX and FORCESEEK Hints with Other Table Hints

The INDEX and FORCESEEK hints can be specified for a query that does not have any existing table hints, or they can be used to replace one or more existing INDEX or FORCESEEK hints in the query. If a query that is matched to a plan guide already has a WITH clause specifying these table hints, the hints specified in the @hints parameter of the plan guide replace those in the query. For example, if the query contains the table hint WITH INDEX (PK_Employee_EmployeeID) for the HumanResources.Employee table and the @hints parameter in the plan guide specifies OPTION (TABLE HINT ( HumanResources.Employee, INDEX( IX_Employee_ManagerID ) ), the query optimizer will use the IX_Employee_ManagerID index.

Table hints other than INDEX and FORCESEEK are disallowed as query hints in the plan guide unless the query already has a WITH clause specifying the table hint. In this case, a matching hint must also be specified as a query hint by using TABLE HINT in the OPTION clause to preserve the semantics of the query. For example, if the query contains the table hint NOLOCK, the @hints parameter of the plan guide must also contain the NOLOCK hint in addition to any INDEX or FORCESEEK table hint in the OPTION clause. See Example C later in this topic. When a table hint other than INDEX or FORCESEEK is specified by using TABLE HINT in the OPTION clause without a matching query hint, or vice versa, error 8702 is raised, indicating that the OPTION clause can cause the semantics of the query to change, and the query fails.

Using INDEX and FORCESEEK Hints with Other Query Hints

If a query that is matched to a plan guide already has an OPTION clause specifying query hints, the query hints specified in the @hints parameter of the plan guide replace those in the query. However, for a plan guide to match a query that already has an OPTION clause, you must include the OPTION clause of the query when you specify the text of the query to match in the sp_create_plan_guide (Transact-SQL) statement. If you want the hints specified in the plan guide to be added to the hints that already exist on the query, instead of replacing them, you must specify both the original hints and the additional hints in the OPTION clause of the plan guide.

Examples

A. Using FORCESEEK

The following example uses the FORCESEEK hint in the @hints parameter of the plan guide. This option forces the optimizer to use an index seek operation to access the data in the HumanResources.Employee table. Note that this can cause the optimizer to use an index other than the one specified in the table hint.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

B. Using multiple table hints

The following example applies the INDEX hint to one table and the FORCESEEK hint to another.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

C. Specifying semantics-affecting hints

The following example contains two table hints in the query: NOLOCK, which is semantic-affecting, and INDEX, which is non-semantic-affecting. To preserve the semantics of the query, the NOLOCK hint is specified in the OPTIONS clause of the plan guide. In addition to the NOLOCK hint, the INDEX and FORCESEEK hints are specified and replace the non-semantic-affecting INDEX hint in the query when the statement is compiled and optimized.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

The following example shows an alternative method for preserving the semantics of the query and allowing the optimizer to choose an index other than the index specified in the table hint. This is done by specifying the NOLOCK hint in the OPTIONS clause (because it is semantic-affecting) and specifying the TABLE HINT keyword with only a table reference and no INDEX hint.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

D. Using TABLE HINT to override an existing table hint

The following example shows how to use TABLE HINT without specifying an INDEX hint to override the behavior of the INDEX table hint specified in the FROM clause of the query. This method allows the optimizer to choose an index other than the index specified in the table hint.

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

See Also

Reference

Query Hints (Transact-SQL)

Table Hints (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

Concepts

Using the FORCESEEK Table Hint