Parameter Sniffing Issue– One solution with Query Hint — Optimize for UNKNOWNPosted: September 17, 2014
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;
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) );