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

  • 1
    What SQL platform and version are you referring to? Did you find any information on those hints online already and what pieces did or didn't make sense between them? – Shaulinator May 16 '18 at 16:40
  • 1
    you can take a look at the following link from Microsoft Documentation – Eduardo Pivaral May 16 '18 at 18:44

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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