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.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

4 Comments on “FORCESEEK hint in SQL Server 2008 – Part II -working with Parameter Sniffing”

  1. Hi Amit,

    I am still trying to understand how is that by forcing a seek the problem of parameter sniffing can be alleviated.

  2. First,

    1. Parameter Sniffing is not a ‘problem’. Its a good thing.

    2. So, FORCESSEK is not really ‘solving’ any ‘problem’ here; my example just shows a scenario where FORCESEEK hint can be used.

  3. Not sure then what did you meant by this then ?

    “…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.”

    Unfortunately I will have to defer with your claim of it not being a ‘problem’.The example you quoted is using 2 different values in where clause.If your query is always going to same set of values repeatedly in the where clause then yes it is not a ‘problem’ otherwise it is a ‘problem’.

  4. Hi Sachin,

    Well ‘problem/solution approach’ is the context of a ‘scenario’ – this being one of the scenarios. Variety of paramerter values canot be termed as a ‘problem’; rather businesses are going to have variety of parameter values in seacrh criterias. I perceive them as ‘scenarios’ – you may percieve them as ‘problems’ – so let us not over-complicate things due to semantics. FORCESEE is just a hint; like many other hints and can be applied in many scenrios; I just explanied one such scenario.

    Thanks for auditing our blogs and articles 😉 – thats why you are the moderator here

    Regards

    Amit

Leave a Reply

Your email address will not be published.