Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

 

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×