Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?


2 Answers 2


The documentation on query hints says

DISABLE_PARAMETER_SNIFFING ... is equivalent to trace flag 4136.

That trace flag was documented in KB980653 which tells us

Enabling trace flag 4136 disables parameter sniffing, which is equivalent to adding an OPTIMIZE FOR UNKNOWN hint to each query which references a parameter.

The documentation on supported trace flags says about 4136

... to accomplish this at the database level, see the PARAMETER_SNIFFING option in ALTER DATABASE SCOPED CONFIGURATION

To accomplish the same result at the query level, add the OPTIMIZE FOR UNKNOWN query hint. The OPTIMIZE FOR UNKNOWN hint doesn't disable the parameter sniffing mechanism, but effectively bypasses it to achieve the same intended result.

.. a second option to accomplish this at the query level is to add the USE HINT 'DISABLE_PARAMETER_SNIFFING' query hint instead of using this trace flag.

It seems to me that these are all a single feature, wrapped in several syntactic formulations, to be applied at different granularity. It can be applied on the instance, a database, the session, a stored procedure, a query or a single parameter within that query. They all do the same thing, just use a different form of words to achieve it. This opinion is supported by the steps to apply when addressing sniffing issues which uses OPTIMIZE FOR and DISABLE.. in turn.

Some of the duplication can be explained in the general move from opaque trace flags to developer-friendly transparent hints. I find "disable sniffing" easier to reason about than "optimize for unknown".

I suppose we may never know if there is any actual code-path difference internally, unless someone with source code access happens to becoming interested in answering. Practically, I don't believe there is.

I looked at the plans produced for very simple queries

dbcc freeproccache;
declare @c int = 1;

select * from dbo.Skewed where c = @c

The query plans for both hints are indistinguishable. Both the actual execution plan and the bound tree (trace flag 8605) are the same.


For most intents and purposes, they are different ways to achieve the same thing.

There is one difference when the RECOMPILE query hint is also specified:

  • OPTIMIZE FOR UNKNOWN does not use the specific parameter value when preparing the execution plan.
  • DISABLE_PARAMETER_SNIFFING allows RECOMPILE to see the parameter value and embed it in the query in place of the parameter.

See USE HINT and DISABLE_PARAMETER_SNIFFING by Dmitry Pilyugin (translation from Russian required).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.