Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 374

18. The final result for the preceding TSQL along with table hints are displayed as follows:
This completes the required steps in implementing table hints for advanced query
performance tuning.
How it works...
In this recipe, we started with a query that is used as a reporting requirement. We need
to obtain the data pertaining to a specific product and the product modified date. We also
checked the estimated execution plan for the query to obtain missing indexes information
where a clustered index scan is shown, and also resource usage such as disk IO and CPU. In
this process, as per the missing index information, we created a new nonclustered index,
but the query optimizer still opted to choose clustered index scan. The new table hint
FORCESEEK allows the query optimizer to force a query to use clustered or nonclustered index
seek access. This hint is also helpful to forceseek when the predicate may not be getting
a good cardinality estimate of data. In such cases, a seek is incorrectly calculated by the
execution plan, which is treated more expensive than the usual data correlation.
At this point, we used the FORCESEEK hint; this hint can be used in the FROM clause of a DML
statement by using a WITH keyword. This overrides the query optimizer's original choice of
the clustered index scan access path and chooses the path of using the newly created index
by using the index seek operation. In addition to the FORCESEEK hint, we used a query hint
to use specific index to choose. The bottom line is only use this new table hint when you are
in total control in your table design and data to perform the updated statistics on the table,
otherwise the SQL Server query optimizer will choose the most optimal path to execute.