SQL Server OPTIMIZE FOR UNKNOWN to disable parameter sniffing at query level

Hi Friends,

Last week, I blogged about Trace Flag 4136:

https://www.sqlservergeeks.com/sql-server-did-you-know-about-trace-flag-4136/

and bunch of other posts related to parameter sniffing:

https://www.sqlservergeeks.com/sql-server-using-optimize-for-query-hint/

https://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/8/forceseek-hint-in-sql-server-2008-part-i

https://www.sqlservergeeks.com/sql-server-forceseek-hint-in-sql-server-2008-part-ii-working-with-parameter-sniffing/

I got a question from reader asking me more about SQL Server OPITMIZE FOR UNKNOW, the purpose, how it works, etc.

Here is a quick example:

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 6
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 2
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

If you observe the output of the plan cache, you will see that the query has been parameterized. This is the default behavior called parameter sniffing. My earlier posts mentioned above can let you know more about it.

Now, I have added the OPTIMIZE FOR UNKNOWN hint which will disable parameterization at query level.

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 6
OPTION (OPTIMIZE FOR UNKNOWN)
GO
SELECT FirstName, LastName, Title FROM Employees 
WHERE EmployeeID = 2
OPTION (OPTIMIZE FOR UNKNOWN)
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
  AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

Notice this time, there is no parameterized version in the plan cache.