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_LOOP||Disables the batch sort operation for optimized nested loops||2340*|
|FORCE_LEGACY_CARDINALITY_ESTIMATION||Enables the ‘old’ estimate mechanism (version 70, used before SQL Server 2014)||9481|
|ENABLE_QUERY_OPTIMIZER_HOTFIXES||Enables query optimizer hotfixes||4199|
|DISABLE_PARAMETER_SNIFFING||Disables parameter sniffing||4136|
|ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES||When estimating complex predicates, use minimum selectivity||4137 (<2014)
|ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS||Enables dependency of join selectivity on predicates by tables||9476|
|ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS||Enables automatic histogram amendment when estimating a predicate by a column regardless of ascending or descending values||4139|
|DISABLE_OPTIMIZER_ROWGOAL||Disable the mechanism for counting the target number of rows when creating a query plan||4138|
|FORCE_DEFAULT_CARDINALITY_ESTIMATION||Determine 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:
select name from sys.all_columns option( use hint( 'FORCE_LEGACY_CARDINALITY_ESTIMATION', 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' ) );
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.