• Article

Using the FORCESEEK Table Hint

The FORCESEEK table hint forces the query optimizer to use only an index seek operation as the access path to the data in the table or view referenced in the query. You can use this table hint to override the default plan chosen by the query optimizer to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.

FORCESEEK applies to both clustered and nonclustered index seek operations. It can be specified for any table or view in the FROM clause of a SELECT statement and in the FROM <table_source> clause of an UPDATE or DELETE statement.

Warning

Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend using hints only as a last resort by experienced developers and database administrators.

Evaluating Query Plans for FORCESEEK Applicability

The FORCESEEK table hint may be useful when the query plan uses a table or index scan operator on a table or view, but an index seek operator may be more efficient. Consider the following query and subsequent execution plan.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

The following execution plan shows that the query optimizer chose a clustered index scan operator to access the data in both tables.

Execution plan with clustered index scan operators

You can force the query optimizer to perform a seek operation on the Sales.SalesOrderDetail table by specifying the FORCESEEK hint as shown in the following query.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

The following execution plan shows the results of using the FORCESEEK hint in the query. A clustered index seek operation is used to access the data in the Sales.SalesOrderDetail table.

Execution plan with clustered index seek operator

Index Union and Intersection Support

The FORCESEEK hint supports index unions and intersections. The hint makes the query optimizer more likely to use these techniques. To avoid slowing the compilation time of simple queries, index unions and intersections are normally only chosen according to rules that take into account the cardinality and selectivity of the columns. However, when the FORCESEEK hint is specified, such rules are bypassed and these techniques are always considered. For example, consider the following query:

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 AND T.b = 2;

If there are separate, nonclustered indexes on columns a and b in table T, an index intersection plan may be chosen. That is, the plan contains a nonclustered index seek operation on column a and a nonclustered index seek operation on column b, and intersects the resulting index key sets before performing a look-up operation into the base table.

In the following example, an index union plan is chosen. That is, the plan contains a seek operation on column a and a seek operation on column b, and unions the resulting index key sets, before performing a look-up operation into the base table.

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;

Using FORCESEEK in Queries That Use LIKE or IN

Query optimizer rules and poor cardinality estimation can also cause the optimizer to perform a table or index scan operation rather than an index seek when a query uses IN or LIKE as search predicates.

The following example demonstrates how the FORCESEEK hint can force the query optimizer to perform an index seek operation rather than a table scan when LIKE or IN are used as search predicates. To view the query execution plans, click the Include Actual Execution Plan toolbar button before running the example.

USE tempdb;
GO
DROP TABLE t;
GO
CREATE TABLE t(i int UNIQUE, j int, vc varchar(100));
CREATE INDEX t_vc ON t(vc);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
SELECT * FROM t WHERE vc LIKE 'Test%';
GO
SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%';
GO
DECLARE @vc varchar(100);
SELECT * FROM t WHERE vc LIKE @vc;
GO
DECLARE @vc varchar(100);
SELECT * FROM t WITH (FORCESEEK) where vc like @vc;
GO

Using FORCESEEK on Views

FORCESEEK can be specified with or without an index hint. When you apply a FORCESEEK table hint to a view or indexed view, the FORCESEEK hint is recursively propagated over all the tables in the expanded version of the view. The index hint, if specified, is ignored. If the underlying tables do not have at least one index each, no plan is found and error 8622 is returned.

When you use the FORCESEEK and NOEXPAND hints together on a reference to an indexed view, the indexed view is used without expanding it first. The FORCESEEK hint is applied directly to the indexed view, which is treated just like a table.

If you apply a FORCESEEK hint to a table reference, the table reference can not participate in indexed view matching. However, other parts of the query that are unaffected by the FORCESEEK hint can participate in indexed view matching. This is comparable to the behavior of indexed view matching when used with INDEX hints.

Best Practice Considerations

We recommend the following best practices:

  • Before using the FORCESEEK table 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.

  • Evaluate the query for items that can cause poor cardinality or cost estimates and remove these items if possible. For example, replace local variables with parameters or literals and limit the use of multi-statement table-valued functions and table variables in the query. For more information about other items to look for, see Statistics Used by the Query Optimizer in Microsoft SQL Server 2005.

  • Do not unnecessarily use the INDEX hint in combination with FORCESEEK. That is, if FORCESEEK alone produces a sufficient plan, also using the INDEX hint may excessively limit the choices the optimizer has. Furthermore, an INDEX hint will cause your query 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.