Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016- VII [ USE HINT query hint argument ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – VII | [ USE HINT query hint argument ]

Microsoft 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. Now the advantage is that we do not have to remember the trace flags and we do not need higher role to perform this.

Sample:-

Option Equivalent trace flag Description
FORCE_LEGACY_CARDINALITY_ESTIMATION TF 9481 Enables 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 4199 Controls query optimizer changes released in SQL Server Cumulative Updates and Service Packs.
DISABLE_PARAMETER_SNIFFING TF 4136 Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR value is used.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES TF 4137 Causes 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 4138 Causes 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 4139 Enable 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 9476 Causes 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 2340 Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins when generating a plan.
FORCE_DEFAULT_CARDINALITY_ESTIMATION TF 2312 Enables 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.

Example

--                               
  
DECLARE @Vals AS VARCHAR(100) = 'SomeValue'
SELECT * from yourtable
WHERE columnname = @vals
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

--

Refer Microsoft LINK below for more details-

1.https://support.microsoft.com/en-in/help/3189813/update-introduces-use-hint-query-hint-argument-in-sql-server-2016

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com