FORCESEEK Hint – SQL Server 2008

Forceseek hint is a new addition to SQL Server 2008. It forces the query optimizer to use an Index seek instead of Index scan. 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 clause of an UPDATE or DELETE statement.

Generally SQL Server is good at selecting the best query plan and hence using hints to override them should be the last option while tuning a query.

Consider the below query in AdventureWorks database,

USE ADVENTUREWORKS
GO
SELECT * FROM Sales.Customer SC
INNER JOIN Sales.CustomerAddress SCA
ON SC.CustomerID=SCA.CustomerID
WHERE SC.TerritoryID <>1

The following is the execution plan which shows that there is a Clustered index scan on both the tables Customer and CustomerAddress. Also the execution plan displays missing index details and suggests to create a new index.

However we can avoid creating any new indexes and force the existing CI scan to seek. In the below query I have just used the table hint Forceseek to make the scan into to a seek,

USE ADVENTUREWORKS
GO
SELECT * FROM Sales.Customer SC WITH (FORCESEEK)
INNER JOIN Sales.CustomerAddress SCA
ON SC.CustomerID=SCA.CustomerID
WHERE SC.TerritoryID <>1

The following is the execution plan for the above query,

One of the scenarios where this hint can be useful in SQL Server is working around with ‘Parameter Sniffing’. This is a technique by which the SQL server query optimization engine sniffs the parameter value from the query and generates an optimized execution plan based on that value.

Consider the below example where I am creating a table and populating with 1 lakh record. Ive created a clustered index on c1 and non clustered index on c2.

IF EXISTS(SELECT name FROM sys.objects WHERE name='T' AND TYPE='U')
DROP TABLE T
GO
CREATE TABLE T (c1 INT, c2 INT, c3 INT, c4 INT, c5 CHAR(200))
CREATE UNIQUE CLUSTERED INDEX Tc1 ON T(c1)
CREATE INDEX Tc2 ON T(c2)
SET NOCOUNT ON
--populate 1lakh records
DECLARE @i INT
SET @i = 0
WHILE @i < 100000
BEGIN
INSERT T VALUES (@i, @i, @i, @i, @i)
SET @i = @i + 1
END

The below query will have a very low selectivity since its fetching close to 99,900 records out of 1lakh records and hence the optimizer will perform an index scan.

--low selectivity
SELECT c1 FROM T WHERE c1 > 101

The below query will perform a clustered index seek since the query fetches only 11,000 records from the 1lakh records which is a highly selective and hence the optimizer opts seek as its cheaper.

--high selectivity
SELECT c1 FROM T WHERE c1 > 89000

The above is known as parameter sniffing where SQL Server sniffs the parameter and prepares the execution plan accordingly.

The problem lies when you run the same query and pass the parameter through a variable. Actually SQL Server is unable to determine the value being passed since its not hard coded. It will know the value only during run time and hence makes a guess and prepares the execution plan.

The guess worked correctly for the below query which used a scan earlier as well due to low selectivity.

--passing the value through variables
DECLARE @i1 INT=101
SELECT c1 FROM T WHERE c1 >@i1

However for the below query the guess done by SQL Server didnt work as it needs to be. Since there is high selectivity there needs to be an index seek operation similar to what happened earlier for the same query where the values were hard coded.

DECLARE @i2 INT=89000
SELECT c1 FROM T WHERE c1>@i2

Now the important thing is if the application uses variables to pass the parameter and if the values are in most cases highly selective there would be an index scan happening as SQL Server wouldnt be able to sniff the parameter. To avoid this and to make a seek operation happen for highly selective fields we can make use of FORCESEEK table hint.

Consider the same query mentioned above and see the difference when using the FORCESEEK table hint,

DECLARE @i2 INT=89000
SELECT c1 FROM T WITH (FORCESEEK) WHERE c1>@i2 

Best practices:

1. Ensure that the statistics are up to date on the tables before using Forceseek table hint

2. Do not unnecessarily use index hint with forceseek and avoid using index hint index (0) with forceseek hint as the former will cause a table scan.

3. Do not use the USE PLAN query hint with forceseek as the forceseek hint is ignored when you do so