sys.dm_exec_valid_use_hints: Every USE HINT Your Server Knows

sys.dm_exec_valid_use_hints lists every USE HINT name that your SQL Server accepts. A hint with a wrong name fails when the query compiles, so the list is the first thing to read.

What sys.dm_exec_valid_use_hints Returns

The view has one column, name, and one row for each hint the server knows. The count depends on the version. The test server runs SQL Server 2025 and returns 35 names. An older server returns fewer, because new releases add hints. The view needs the VIEW SERVER STATE permission, which is VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

SELECT COUNT(*) AS HintCount FROM sys.dm_exec_valid_use_hints;

The sys.dm_exec_valid_use_hints list is too long to read in one piece. Most names start with a verb, so grouping by the first word gives a map.

SELECT LEFT(name, CHARINDEX(N'_', name)) AS Family, COUNT(*) AS Hints
FROM sys.dm_exec_valid_use_hints
GROUP BY LEFT(name, CHARINDEX(N'_', name))
ORDER BY Family;

The DISABLE_ family is the largest. Each hint switches off one optimizer feature for one query. The ASSUME_ hints change how the optimizer estimates filters. The QUERY_ family holds eight compatibility level hints, from 100 to 170, and one plan profile hint.

To read every name, list them. The names below come from SQL Server 2025 (17.0.5005.3), and a lower version returns fewer.

SELECT name FROM sys.dm_exec_valid_use_hints ORDER BY name;

ABORT_QUERY_EXECUTION
ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP
ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP
ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
DISABLE_BATCH_MODE_ADAPTIVE_JOINS
DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK
DISABLE_CE_FEEDBACK
DISABLE_DEFERRED_COMPILATION_TV
DISABLE_DOP_FEEDBACK
DISABLE_INTERLEAVED_EXECUTION_TVF
DISABLE_MEMORY_GRANT_FEEDBACK_PERSISTENCE
DISABLE_OPTIMIZED_NESTED_LOOP
DISABLE_OPTIMIZED_PLAN_FORCING
DISABLE_OPTIMIZER_ROWGOAL
DISABLE_PARAMETER_SNIFFING
DISABLE_RESULT_SET_CACHE
DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK
DISABLE_TSQL_SCALAR_UDF_INLINING
DISALLOW_BATCH_MODE
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS
ENABLE_QUERY_OPTIMIZER_HOTFIXES
FORCE_DEFAULT_CARDINALITY_ESTIMATION
FORCE_LEGACY_CARDINALITY_ESTIMATION
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_170
QUERY_PLAN_PROFILE

A Misspelled Hint Fails at Compile Time

SQL Server checks every hint name before it runs the query. A typo does not fall back to no hint. The query fails, and no rows come back. Names are not case sensitive, so lowercase works. The next query drops the letter I from SNIFFING.

SELECT TOP (3) name FROM sys.objects OPTION (USE HINT('DISABLE_PARAMETER_SNIFFNG'));

Msg 10715, Level 15, State 1, Line 1
'DISABLE_PARAMETER_SNIFFNG' is not a valid hint.

A deployment script can check sys.dm_exec_valid_use_hints first. The same hint can be missing on an older server, and the check answers before any query fails.

SELECT v.HintName,
       CASE WHEN h.name IS NULL THEN N'Unknown' ELSE N'Known' END AS HintStatus
FROM (VALUES (N'DISABLE_PARAMETER_SNIFFING'), (N'DISABLE_PARAMETER_SNIFFNG')) AS v(HintName)
LEFT JOIN sys.dm_exec_valid_use_hints AS h ON h.name = v.HintName;

Six Hints Worth Knowing

Each hint below is documented, and each applies to one query. The table says what it does in one line.

A related hint turns off one join feature. It is covered in Disable Adaptive Join for One Query or a Whole Database.

A USE HINT affects only the statement that carries it. Other queries and other sessions compile as before, and the next compile of the same statement uses the hint again. The hint clause itself needs SQL Server 2016 SP1 or later.

Apply a Hint and Check That It Worked

A hint that runs without an error has not proven anything. The plan shows what changed. The first example uses DISABLE_PARAMETER_SNIFFING. Both queries take a parameter through sp_executesql, and the comment at the end only labels the query text.

EXEC sys.sp_executesql N'SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = @t; -- sniff test plain', N'@t int', @t = 231;

EXEC sys.sp_executesql N'SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = @t OPTION (USE HINT(''DISABLE_PARAMETER_SNIFFING'')); -- sniff test hinted', N'@t int', @t = 231;

Now read the compiled parameter value from each cached plan. A plan compiled with sniffing records the value it saw. A plan compiled without it records none.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT CASE WHEN st.text LIKE N'%sniff test hinted%' THEN N'With DISABLE_PARAMETER_SNIFFING' ELSE N'No hint' END AS Query,
       qp.query_plan.value('(//ParameterList/ColumnReference/@ParameterCompiledValue)[1]', 'nvarchar(40)') AS CompiledValue
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE st.text LIKE N'%-- sniff test %' AND st.text NOT LIKE N'%dm_exec_cached_plans%'
ORDER BY Query;

The plain query compiled with the value 231. The hinted query has no compiled value, so SQL Server did not look at the parameter. The second example changes the row estimation model. Each plan carries the model version it used, and the hint should change it.

SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = 231 AND max_length = 256; -- model test plain
GO
SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = 231 AND max_length = 256 OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')); -- model test hint
GO
SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = 231 AND max_length = 256 OPTION (QUERYTRACEON 9481); -- model test flag
GO
SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = 231 AND max_length = 256 OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130')); -- model test lv130
GO
SELECT COUNT(*) AS N FROM sys.all_columns WHERE system_type_id = 231 AND max_length = 256 OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')); -- model test lv110

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT CASE WHEN st.text LIKE N'%-- model test hint%' THEN N'USE HINT'
            WHEN st.text LIKE N'%-- model test lv130%' THEN N'Level 130 hint'
            WHEN st.text LIKE N'%-- model test lv110%' THEN N'Level 110 hint'
            WHEN st.text LIKE N'%-- model test flag%' THEN N'QUERYTRACEON 9481'
            ELSE N'No hint' END AS Query,
       qp.query_plan.value('(//StmtSimple/@CardinalityEstimationModelVersion)[1]', 'int') AS ModelVersion
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE st.text LIKE N'%-- model test %' AND st.text NOT LIKE N'%dm_exec_cached_plans%'
ORDER BY Query;

The default plan uses model 170, the model of SQL Server 2025. The hint and the trace flag both produced model 70, the legacy model. The compatibility level hints work the same way. Level 130 gave model 130, and level 110 gave model 70, as the last two rows show.

Why USE HINT Is Safer Than QUERYTRACEON

Both options reach the same result for one query. They differ in who can use them and how readable they are. QUERYTRACEON takes a trace flag number, and the number says nothing about its effect. A login without the sysadmin role is refused with Msg 2571, because the option needs the DBCC TRACEON permission. The same login ran USE HINT without trouble.

A hint name also describes itself. FORCE_LEGACY_CARDINALITY_ESTIMATION needs no lookup, and 9481 needs one. Several hints replace a trace flag. DISABLE_OPTIMIZER_ROWGOAL stands in for flag 4138, and ENABLE_QUERY_OPTIMIZER_HOTFIXES for flag 4199. The older post USE HINT: Switching Off One Optimizer Rule Without Trace Flags goes deeper on one rule.

You could argue that a hint is a risk. It stays in the code after the cause is gone, and it blocks later improvements. That is fair. A hint on one query is still smaller than a switch for the whole database. Fix the statistics or the index first, and keep the hint as a recorded exception.

What to Remember

Read sys.dm_exec_valid_use_hints before you write a hint, and check a name in it before you deploy. Confirm the effect in the plan, not in the absence of an error. Prefer USE HINT to QUERYTRACEON, because it needs no sysadmin role and explains itself. Every script here only reads, so there is nothing to clean up.

A hint is not a fix, it is a decision you have to write down.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.