Parameter Sniffing Issue– One solution with Query Hint — Optimize for UNKNOWN

I am trying different solutions to resolve parameter sniffing issues for a few existing stored procedures.
I have tried available solutions listed below and I found Option(Optimize for UNKNOW) works well for them. I have listed all these methods to deal with parameter sniffing and three forms to use Optimize for UNKNOWN.

1.Local variables to pass the parameters’ values;
2. Option(RECOMPILE)
Recompile the query when it is executed using the RECOMPILE hint and it is a very CPU intensive and loses the benefits of caching query plans;

3. Optimize FOR with a static value (may not easy all the time);

USE AdventureWorks;
DECLARE @city_name nvarchar(30);
SET @city_name = 'Ascheim'; 
SELECT * FROM Person.Address
WHERE City = @city_name 
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );


4. Forcing the use of a specific index;
Sample from MSDN:

 EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';


5. Optimize FOR UNKOWN
Three forms to use Optimize FOR UNKOWN:



 OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );


Some refrences:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s