FORCESEEK hint in SQL Server 2008 – Part II -working with Parameter Sniffing

Hi Friends,

In Part 1, I introduced the FORCESEEK hint in SQL server 2008 that can force the query optimizer to perform a seek instead of a scan on an index. However, I did not discuss some scenarios where this hint can be userful. One the of the scenarios where this hint can be useful in SQL Server is working around with ‘Parameter Sniffing’.

But first, What is Parameter Sniffing??

Parameter Sniffing is a technique by which the the sql server query optimization engine sniffs the parameter value from the query and generates an optimized execution plan based on that value.

let us take an example. This example uses the northwind database. You can download the northwind database from the following link and restore it in sql server 2008.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Run the following query and observe the result set and the execution plan:

--
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'05022'
--

You will see that only 1 record is returned, that means, very high selectivity. And now if u see the execution plan you will notice that sql server does a fine job by performing seek and lookups.

now run the following query and observe the result set and the execution plan:
(yes, i have changed the postal code value in this query  )

--
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'05022'
--

You will see that around 30 records are returned, that means, low selectivity. And now if u see the execution plan you will notice that sql server does a fine job by performing a clustered index scan can becuase at this time seek will be expensive.

So, this is known as parameter sniffing, where sql server sniffs the parameter value and prepares the execition plan accordingly.

So where is the problem??

The problem is when u run the same queries and this time pass the parameter vlaues using variables and not by hard cording. when u pass the values using variables, sql server deos not know the parameter value until runtime and makes a hard coded guess. let me prove it:

run both the queries now as shown below and observe the execution plan: (run one query at a time since varibale name is same)
(note that this time i am using variables rather than hardcoding parameter values)

--simple query with a variable 05022
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'05022';
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode;
 
--simple query with a variable 83720
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'83720';
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode;

What did u notice in the execution plan?

You will see that both the execution plan use clustered index scan even though parameter values are different. Comparing this with previous scenario, sql server rightly used seek in case of high selectivity and scan in case of low selectivity.

This has happned because this time sql server could not sniff parameter values as the values were not available until run time since we used variables and sql server used a hardcoded gues based on 30% selectivity (a general mechanism followed by sql server)

now, the crux is:

if your application uses variables, and most of the times these variable values result in high selectivity, how can you force sql server to perform a seek operation instead of scan.

Yes, we can use FORCESEEK

run the following queries again and see the execution plan (this time I have added the forceseek table hint to ensure a seek is performed)

--simple query with a variable 05022
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'05022';
SELECT [OrderId], [OrderDate]
FROM [Orders] WITH (FORCESEEK)
WHERE [ShipPostalCode] = @ShipCode
 
 
--simple query with a variable 83720
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'83720';
SELECT [OrderId], [OrderDate]
FROM [Orders] with (FORCESEEK)
WHERE [ShipPostalCode] = @ShipCode;

You will now observe in the execution plan that seek operation is performed as expected for both the queries.

Remember:

-use hints as a last resort for performance tuning (the optimizer does a good job most of the times)
-FORCESEEK can be useful in many scenarios, this is just one of them.
-in this scenario, i insisted that most of the times, the resultset will be hihgly selective so i forced a seek. But your scenario could be different. So this is just one scenario and an example based on that scenario. There could be many other scenarios.
-i can also use other hints like OPTIMIZE FOR, RECOMPILE, etc.. but the problem/solution will change slightly. I am not discussing them as it is out of scope here.

I hope I was able to explain Parameter Sniffing and how FORCESEEK can be used to workaround them.