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.

1_SQL_Server_OPTIMIZE_FOR_UNKNOWN_to_disable_parameter_sniffing_at_query_level

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.

2_SQL_Server_OPTIMIZE_FOR_UNKNOWN_to_disable_parameter_sniffing_at_query_level

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

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 →

Leave a Reply

Your email address will not be published. Required fields are marked *