Replace Trace Flag with ‘USE HINT’
January 23, 2021
Introduced in SQL Server 2016 (13.x) SP1 and Azure SQL Database is the USE HINT option. At the time of writing this article, there are 23 of those. You can see an up to date list by running Dynamic Management View sys.dm_exec_valid_use_hints.
With the introduction of this feature, we can replace some of the trace flags with hints. I personally like hints over trace flags (if I have to choose between two devils). That way someone reading the code have some idea what the hint is about instead of remembering the trace flag numbers.
Here is a list of Trace Flags and Hints that we can replace:
| Trace Flag | Hint |
|---|---|
| 9476 | ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS |
| 4137 | SQL Server 2012 (11.x) and earlier versions ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES |
| 9471 | SQL Server 2014 (12.x) or higher ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES |
| 2340 | DISABLE_OPTIMIZED_NESTED_LOOP |
| 4138 | DISABLE_OPTIMIZER_ROWGOAL |
| 4136 | DISABLE_PARAMETER_SNIFFING |
| 4139 | ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS |
| 4199 | ENABLE_QUERY_OPTIMIZER_HOTFIXES |
| 9481 | FORCE_DEFAULT_CARDINALITY_ESTIMATION FORCE_LEGACY_CARDINALITY_ESTIMATION |
