How to optimize the performance of the stored procedure that often scans tables using forceseek table hint in sql server
Suppose you have a stored procedure that execute quite often and the stored procedure joins data from two or more tables. When you look at the query execution plan most of the time the stored procedure make use of table scans rather than indexes when the estimated rows do not match the actual rows on one of the tables. One way to optimize the performance of your stored procedure could be by using FORCESEEK table hint in the stored procedure.
Let’s see this in action:
Script without FORCESEEK
In this case the server is not sure what index to use and it did a clustered index scan.
Script with FORCESEEK
In this case it was doing a clustered index seek and a key lookup.
You need to make sure the following in your database before using FORCESEEK
- Your statistics should be current and accurate.
- If possible find items that could cause poor cardinality or cost estimate and remove them.
- Don’t combine index hints with FORCESEEK unnecessarily.
- Don’t combine INDEX(0) with FORCESEEK as INDEX(0) force scan of tables
- Don’t combine FORCESEEK with USE PLAN as your FORCESEEK will be ignored.
Warning: Is it is described in Microsoft site query optimizer usually select best plan and hints should only be used as last resort by experienced developers and DBAs
Reference used:
http://technet.microsoft.com/en-us/library/bb510478(v=sql.105).aspx
http://technet.microsoft.com/en-us/library/bb677261(v=sql.100).aspx
