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.
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:
So the impulse might be to just fall back to the Legacy CE – perhaps it would do better? Let’s verify:
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:
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?
No problem from a code execution perspective. Fully compatible and USE HINT takes precedence.
What about using conflicting new hints with QUERYTRACEON?
In these cases, where conflicting behaviors are introduced, this will be the output:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.