Developers Choice: USE HINT query hints

Iron Contributor

Mar 23, 2019

First published on MSDN on Nov 21, 2016

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 ( http://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 OldCE 9471 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

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

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

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:

Which derive from applying minimum selectivity for filters:

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.