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 ?|
|ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES||4137 for OldCE
9471 for NewCE
|ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS||9476 on NewCE|
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
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
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.
Pedro Lopes (@sqlpto) – Senior Program Manager