Hi Friends,

Last week, I blogged about Trace Flag 4136:

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

and bunch of other posts related to parameter sniffing:

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

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

http://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:

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.

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