Can you tell me what is difference between
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and
OPTION (OPTIMIZE FOR UNKNOWN)?
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 TRACEON(3604); dbcc freeproccache; declare @c int = 1; select * from dbo.Skewed where c = @c OPTION (QUERYTRACEON 8605, USE HINT('DISABLE_PARAMETER_SNIFFING')); -- OPTION (QUERYTRACEON 8605, OPTIMIZE FOR UNKNOWN);
The query plans for both hints are indistinguishable. Both the actual execution plan and the bound tree (trace flag 8605) are the same.