Share via

Developers Choice: USE HINT query hints

  • Article

The use of Trace Flags has always been understood as a sort of ‘hack’, and a solution that is hard to manage and sometimes understand (namely because it depends on a number).

Over the years, users have seen several knobs added to SQL Server to influence the Query Optimizer. These trace flags are documented in Books Online (https://aka.ms/traceflags).

Besides being hard to understand, using trace flags in a query involves QUERYTRACEON and also requires SA permissions. In many companies this can be difficult to implement as it usually requires giving elevated permissions to application users, which can be a security liability. The user community has made that clear in Connect.

So with all this in mind, with SQL Server 2016 SP1, we made available a new class of hints under USE HINT. These are documented query hints that add clarity and ease of use, does not require elevated privileges, and also closes a gap between SQL Server and Azure SQL DB (USE HINT is available in both platforms).

Also aligns with some new database-level settings added in SQL Server 2016 that mimic the behavior of a few common Trace Flags.

In this first release address most common scenarios for QUERYTRACEON usage, but going forward, any new USE HINT hints and database-level settings may not have a respective Trace Flag counterpart.

Below is the list of supported hints in this first release. Refer to the page Query Hints (Transact-SQL) for further information on these new class of hints.

USE HINT TF ? DB Option ?
DISABLE_OPTIMIZED_NESTED_LOOP 2340  
FORCE_LEGACY_CARDINALITY_ESTIMATION 9481 Yes
ENABLE_QUERY_OPTIMIZER_HOTFIXES 4199 Yes
DISABLE_PARAMETER_SNIFFING 4136 Yes
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES 4137 for OldCE9471 for NewCE  
DISABLE_OPTIMIZER_ROWGOAL 4138  
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS 4139  
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS 9476 on NewCE  
FORCE_DEFAULT_CARDINALITY_ESTIMATION 2312  

So how can I use these new hints?

Here’s is an example of a query using the New CE running in AdventureWorksDWCTP3:

 SELECT * FROM FactInternetSales fis INNER JOIN DimProduct dp ON fis.ProductKey = dp.ProductKey
WHERE CurrencyKey = 98 AND SalesTerritoryKey = 10
GO

image

Notice the SORT WARNING, and the difference between estimated and actual rows. Estimations drive memory grants, and without enough memory the sort ends up spilling.

In this case we see the New CE is underestimating. These underestimations go up the tree to the JOIN.

So the impulse might be to just fall back to the Legacy CE – perhaps it would do better? Let’s verify:

 SELECT * FROM FactInternetSales fis INNER JOIN DimProduct dp ON fis.ProductKey = dp.ProductKey
WHERE CurrencyKey = 98 AND SalesTerritoryKey = 10
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
GO

image

Actually no. Let’s try generating a plan using minimum selectivity for single-table filters (such as the case here), using one of the new hints:

 SELECT * FROM FactInternetSales fis INNER JOIN DimProduct dp ON fis.ProductKey = dp.ProductKey
WHERE CurrencyKey = 98 AND SalesTerritoryKey = 10
OPTION (USE HINT('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'))
GO

image

We can see how we have very good estimations, and no SORT WARNING anymore.

Let’s compare memory grant requirements to run both queries. Clearly proper estimations had the desired effect of having a warning-free execution:

image

Which derive from applying minimum selectivity for filters:

image

What about using the new hints with QUERYTRACEON?

 -- Same query compiled with legacy CE hint and legacy CE TF
SELECT AddressID
FROM Person.[Address]
WHERE City = N'Ballard' AND [PostalCode] = '98107'
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'), QUERYTRACEON 9481)
GO

No problem from a code execution perspective. Fully compatible and USE HINT takes precedence.

What about using conflicting new hints with QUERYTRACEON?

 -- Same query compiled with legacy CE hint and New CE TF
SELECT AddressID
FROM Person.[Address]
WHERE City = N'Ballard' AND [PostalCode] = '98107'
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'), QUERYTRACEON 2312)
GO

In these cases, where conflicting behaviors are introduced, this will be the output:

 Msg 10781, Level 16, State 1, Line 36
Querytraceon 2312 and optimizer hint 'FORCE_LEGACY_CARDINALITY_ESTIMATION' specify conflicting behaviors. Remove one of them and rerun the query.

There’s also a new DMV sys.dm_exec_valid_use_hints that lists supported name hints. This is not meant a dictionary, but rather as a tool that will allow a developer to determine if a specific hint is available in a given version, where code is intended to execute, before it actually does.

image

Pedro Lopes (@sqlpto) – Senior Program Manager

Comments

  • Anonymous
    November 21, 2016
    Very nice way to control optimizer behavior, thank you for that!
  • Anonymous
    November 22, 2016
    Should certainly help for going from below SQL 2014 to SQL 2016 queries. Thank you.