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;
GO
DECLARE @city_name nvarchar(30);
 
SET @city_name = 'Ascheim'; 
SELECT * FROM Person.Address
WHERE City = @city_name 
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
GO
 

 

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)))';
GO
 
 

 

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


 --1.
 Option (OPTIMIZE FOR UNKNOWN)

 --2.
 Option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))

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

 

Some refrences:

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx
http://msdn.microsoft.com/en-us/library/ms181714(SQL.100).aspx

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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