New Query Hints Including USE HINT in SQL Server 2016 SP

This article is an introduction to the query hint overview that appeared in SQL Server 2016 SP1.

There are many different thoughts on using hints in queries: some people say that you should never use them, while others insist on using hints if anything works incorrectly.

I think that a hint is a tool and it can be useful if it is suitable for the case. I use hints when there are two conditions. The first one is that I totally understand the reason for the bad plan, and the second one is that there are no other ways to eliminate this reason.

In addition, it is important to remember that hints may be more restrictive and less restrictive to limit the optimizer actions. When applicable, it is better to use less restrictive. For example, to limit join types, it is a good idea to use the query hint, rather than a join hint, as the latter one restricts the order of joins as well.

There are a lot of query hints. However, we will explore the ones that appeared in SQL Server 2016 SP1 and that can be used with USE HINT. You can get them using the sys.dm_exec_valid_use_hints view. Below, you can find a table of available hints with their short description:

DISABLE_OPTIMIZED_NESTED_LOOPDisables the batch sort operation for optimized nested loops 2340*
FORCE_LEGACY_CARDINALITY_ESTIMATIONEnables the ‘old’ estimate mechanism (version 70, used before SQL Server 2014)9481
ENABLE_QUERY_OPTIMIZER_HOTFIXESEnables query optimizer hotfixes 4199
DISABLE_PARAMETER_SNIFFINGDisables parameter sniffing 4136
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATESWhen estimating complex predicates, use minimum selectivity 4137 (<2014)
9471 (>=2014)
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERSEnables dependency of join selectivity on predicates by tables9476
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYSEnables automatic histogram amendment when estimating a predicate by a column regardless of ascending or descending values 4139
DISABLE_OPTIMIZER_ROWGOALDisable the mechanism for counting the target number of rows when creating a query plan 4138
FORCE_DEFAULT_CARDINALITY_ESTIMATIONDetermine a version of the evaluation model compatible with the database level

To apply the hint, it is necessary to add USE HINT in the OPTION statement and list one or several hints in brackets:

We used to execute queries with trace flags, rather than using hints. However, to use trace flags, you need to remember what flag number is responsible for, as well as to have SA privileges. Though there is a workaround for the latter one, it is not comfortable.

In my further publication, we will analyze in details the DISABLE_OPTIMIZED_NESTED_LOOP hint.


The article was translated by Codingsight team with the permission of the author.

Dmitry Pilugin

Dmitry Pilugin

Dmitry Pilugin is a SQL Server DB developer from Russia, Moscow. He works with a SQL Server for more than ten years. Most of the time he was involved as a developer of corporate information systems based on SQL Server data platform. Currently he works as a database developer lead, responsible for the development of production databases in the media research company. He is also an occasional speaker at various community events and tech conferences. Favorite topic to present is Query Processor and any things related to it. Dmitry is a Data Platform Microsoft MVP since 2014.
Dmitry Pilugin