Update introduces USE HINT query hint argument in SQL Server 2016

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.

OptionEquivalent trace flagDescription
FORCE_LEGACY_CARDINALITY_ESTIMATION TF 9481Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier versions, regardless of the compatibility level of the database.
ENABLE_QUERY_OPTIMIZER_HOTFIXES TF 4199Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs.
DISABLE_PARAMETER_SNIFFING TF 4136Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR value is used.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES TF 4137Causes SQL Server to generate a plan by using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 and earlier versions.
DISABLE_OPTIMIZER_ROWGOAL TF 4138Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS TF 4139Enable automatically generated quick statistics (histogram amendment) regardless of key column status. If this option is used, regardless of the leading statistics column status (ascending, descending, or stationary), the histogram used to estimate cardinality will be adjusted at query compile time.
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS TF 9476Causes SQL Server to generate a plan by using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions.
DISABLE_OPTIMIZED_NESTED_LOOP TF 2340Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins when generating a plan.
FORCE_DEFAULT_CARDINALITY_ESTIMATION TF 2312Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 through SQL Server 2016 versions, dependent of the compatibility level of the database.

More Information

This update is included in Service Pack 1 for SQL Server 2016.

About SQL Server 2016 builds
About SQL Server 2016 builds
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.
Свойства

Номер статьи: 3189813 — последний просмотр: 16 нояб. 2016 г. — редакция: 1

Отзывы и предложения