Update introduces USE HINT query hint argument in SQL Server 2016

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core

Summary


This update introduces a new query hint argument, USE HINT, which lets you drive the query optimizer without elevated credentials or without being a member of the sysadmin server role. The syntax of this new query hint resembles the following:

<query_hint > ::=
{
USE HINT(N'key' [ [, ]...n ])
}

This update also introduces the following hint options that can be used with the USE HINT argument.
 
Option Equivalent trace flag Description Applies To
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS TF 9476 Causes SQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the query optimizer Cardinality Estimation model of SQL Server 2014 (12.x) or newer.  
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES TF 4137 Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. This hint name is parallel to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 (11.x) and earlier versions, and has similar effect when trace flag 9471 is used with cardinality estimation model of SQL Server 2014 (12.x) or higher.  
DISABLE_BATCH_MODE_ADAPTIVE_JOINS   Disables batch mode adaptive joins.  starting in SQL Server 2017
DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK   Disables batch mode memory grant feedback.  starting in SQL Server 2017
DISABLE_DEFERRED_COMPILATION_TV   Disables table variable deferred compilation.  starting in SQL Server 2019
DISABLE_INTERLEAVED_EXECUTION_TVF   Disables interleaved execution for multi-statement table-valued functions.  starting in SQL Server 2017
DISABLE_OPTIMIZED_NESTED_LOOP TF 2340 Instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan.  
DISABLE_OPTIMIZER_ROWGOAL TF 4138

Causes SQL Server to generate a plan that doesn't use row goal modifications with queries that contain these keywords:

  • TOP
  • OPTION (FAST N)
  • IN
  • EXISTS
 
DISABLE_PARAMETER_SNIFFING TF 4136 Instructs query optimizer to use average data distribution while compiling a query with one or more parameters. This instruction makes the query plan independent on the parameter value that was first used when the query was compiled. Use this hint to override Database Scoped Configuration setting PARAMETER_SNIFFING=OFF.  
DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK   Disables row mode memory grant feedback. starting in SQL Server 2019
DISABLE_TSQL_SCALAR_UDF_INLINING   Disables scalar UDF inlining. starting in SQL Server 2019
DISALLOW_BATCH_MODE   Disables batch mode execution. starting in SQL Server 2019
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS TF 4139 Enables automatically generated quick statistics (histogram amendment) for any leading index column for which cardinality estimation is needed. The histogram used to estimate cardinality will be adjusted at query compile time to account for actual maximum or minimum value of this column.  
ENABLE_QUERY_OPTIMIZER_HOTFIXES TF 4199 Enables query optimizer hotfixes (changes released in SQL Server Cumulative Updates and Service Packs). Use this hint to override Database Scoped Configuration setting QUERY_OPTIMIZER_HOTFIXES=ON.  
FORCE_DEFAULT_CARDINALITY_ESTIMATION TF 9481 Forces the Query Optimizer to use Cardinality Estimation model that corresponds to the current database compatibility level. Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON.  
FORCE_LEGACY_CARDINALITY_ESTIMATION TF 9481 Forces the query optimizer to use Cardinality Estimation model of SQL Server 2012 (11.x) and earlier versions. Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON.  
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n   Forces the query optimizer behavior at a query level. This behavior happens as if the query was compiled with database compatibility level n, where n is a supported database compatibility level. Refer to sys.dm_exec_valid_use_hints for a list of currently supported values for n starting in SQL Server 2017 (14.x) CU10

For more information, see Hints (Transact-SQL) - Query.

More Information


This update is included in Service Pack 1 for SQL Server 2016.
 
Scenarios in which SQL Server query optimizer (QO) behavior must be hinted are fairly common, and traditionally they are addressed by using several (documented and undocumented) trace flags. However, when trace flags are set globally, they may have an adverse effect on other workloads. Additionally, enabling them per-session is not practical with existing applications, and enabling them per-query with OPTION QUERYTRACEON requires membership in the sysadmin fixed server role. (Although you can work around this behavior by using a plan guide or a stored procedure, elevated credentials are still required.)

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior, They may be difficult to manage and understand.

For information about trace flags, see the Trace Flags (Transact-SQL) topic on the Microsoft Developer Network (MSDN) website.

References


Learn about the terminology  that Microsoft uses to describe software updates.