SQL Server FORCESEEK Hint February 22nd, 2013

Vinod Kumar

Generally I am not a big fan of using hints inside SQL Server. Having said that, have seen many customers use wrong hints and not knowing what they are used for. From SQL Server 2008, there were several interesting new hints introduced that are lesser known to developers. One such hint is around FORCESEEK. Please use this with care and after proper testing in your environment.

The FORCESEEK table hint instructs the optimizer to only use an index seek (clustered or non-clustered index) as the access path for a given table. If you also specify an index hint, it will only produce a query plan with a seek on the specified index. If an index seek plan is not found, or is not found using the specified index, the query compilation will fail with error 8622:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

With only an index hint we can force the use of a particular index but we cannot force an index seek. If you forced a particular index, the query might still perform a scan instead of an index seek. Here is below is one such example:

CREATE TABLE test (c1 INT NOT NULL PRIMARY KEY, c2 INT)

GO

CREATE INDEX i ON test (c2)

GO

SET SHOWPLAN_TEXT ON

GO

SELECT * FROM test WHERE c1 = 0 or c2 = 100

SELECT * FROM test WITH (INDEX(1)) WHERE c1 = 0 or c2 = 100

GO

SET SHOWPLAN_TEXT OFF

The output will show:

StmtText

————————————————————————–

  |–Index Scan(OBJECT:([tempdb].[dbo].[test].[i]),  WHERE:([tempdb].[dbo].[test].[c1]=(0) OR [tempdb].[dbo].[test].[c2]=(100)))

 

(1 row(s) affected)

 

StmtText

————————————————————————–

  |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[test].[PK__test__3213663B2D517BEF]), WHERE:([tempdb].[dbo].[test].[c1]=(0) OR [tempdb].[dbo].[test].[c2]=(100)))

Now if we try to use the FORCESEEK hint to the above query like:

SELECT * FROM test WITH (FORCESEEK)

WHERE c1 = 0 or c2 = 100

If the query doesn’t get satisfied using this hint, we will still get the error 8622. And the output is interesting to see for above query:

StmtText

————————————————————————-

  |–Stream Aggregate(GROUP BY:([tempdb].[dbo].[test].[c1]) DEFINE:([tempdb].[dbo].[test].[c2]=ANY([tempdb].[dbo].[test].[c2])))

       |–Merge Join(Concatenation)

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[test].[PK__test__3213663B2D517BEF]), SEEK:([tempdb].[dbo].[test].[c1]=(0)) ORDERED FORWARD)

            |–Index Seek(OBJECT:([tempdb].[dbo].[test].[i]), SEEK:([tempdb].[dbo].[test].[c2]=(100)) ORDERED FORWARD)

The FORCESEEK hint is useful to force seeks when the predicate may not be getting a good cardinality estimate, a seek is incorrectly costed as more expensive than a scan because of a number of reasons. It is also quite useful for forcing index union (e.g., test.c1 = x AND test.c2 = y) or index intersection (e.g., test.c1 = x OR test.c2 = y) plans for queries with multiple predicates on the same table, as shown in the example above.

I have always recommended to keep away from hints, but if it is a third party code and you don’t want to create any indexes etc but want to keep your logic separate from the systems source code, then using hints like these are surely something to be considered.

Tags: , , , , , ,

This entry was posted on Friday, February 22nd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply