Forceseek table hint to the rescue

SQL Forceseek table hint was added in SQL Server 2008 , but it was until recently I had a legitimate use for it. I was in the midst of a large data archiving job and the runtimes were slower than expected. Checking the execution plans there were some mysterious index scans.

I was confused. It was a highly selective field , so I expected the index seek to occur. I doublechecked the sql statistics were updated and cleared the cache . I would have expected SQL Server to guess during the runtime and generate an index seek, but this was not occurring.

After a while I realised the issue. The parameter was being passed through as a variable. SQL Server wasn’t able to parameter sniff. OK , no be big deal as I was able to use FORCESEEK , this cleared my problem, and the runtime improved massively.

The FORCESEEK hint constrains the query optimizer to use only an index seek for definining the access path to the data. Here are some examples.

use [master]
go
select * from sys.sysobjects WITH (FORCESEEK) where name = 'spt_monitor'
select name from sys.sysobjects WITH (FORCESEEK,INDEX(IX_xxxxx)) where name = 'spt_monitor'

Although FORCESEEK helped me out in these set of circumstances , I can see it has potential to be abused. My usage was for an adhoc one-off data archive process.  

Disadvantages of using FORCESEEK

1. Using Forceseek on queries is it could make you feel like a hero today , but could lead to a deep crisis tomorrow. One of the issues you need to plan for is problems that will appear tomorrow. That may mean new indexes. The problem of hardcoding hints will make the query inflexible to the benefits the new index has to offer
2. What happens when the index is dropped? You’ll need to check out all the code with the FORCESEEK – index option.
3. During upgrades the SQL Server Query Optimizer may have enhancements which improve the access paths to the indexes. Hardcoding hints can limit the implementation of these enhancements.

Read More on SQL Server performance

SQL Server Performance Checklist

SQL Server Performance Killers