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:
|
|
|
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.
Each new build for SQL Server 2016 contains all the hotfixes and all the security fixes that were included with the previous build. We recommend that you install the latest build 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.
