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:

[table id=8 /]

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.