First published on MSDN on Sep 19, 2018
Over the years you have read a number of blogs advocating for or against trace flags that influence SQL Server’s query execution model. You can see a number of query execution related trace flags are documented at
. However, trace flags are deemed a hacky way of influencing SQL Server’s behaviors – they’re named trace flags after all.
This is one of the reasons we introduced USE HINT query hints back in SQL Server 2016 SP1. The new class of hints is meant to provide knobs that are sometimes required, in a fully supported way, without having to remember trace flag numbers: which one is easier to understand below? Yet both queries implement the same behavior – to not use row goal during query optimization.
SELECT TOP 1000 col1
OPTION (QUERYTRACEON 4138);
SELECT TOP 1000 col1
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));
You can read more about USE HINTS
, and on row goal
As you probably understood by now, the existing USE HINT hints are meant to tweak the Query Optimizer for a specific intent, including the hints FORCE_
_CARDINALITY_ESTIMATION and FORCE_
_CARDINALITY_ESTIMATION. On the latter, the “default” cardinality estimation (CE) is the CE model mapped to the
database compatibility level
in use (in turn, using a newer compatibility level also implies use of query optimizer fixes under
trace flag 4199
). Talking about the versions of the CE is a whole topic by itself, so I won’t get into much detail here, and you can read more on the versions of the CE
What is important to retain is that we have moved away from the notion of “New CE” and “Old CE”. Because in every version we have made changes and fixes to query optimization, there is no all-up “New CE” anymore. Referring to CE versions becomes clearer, and so we have CE70 (default CE for SQL Server 7.0 through SQL Server 2012), and starting with SQL Server 2014, the number that is default for each engine version: CE120 for 2014, CE130 for 2016, CE140 for 2017, and so on.
With that, let’s introduce a
You have upgraded your SQL Server from 2014 to SQL Server 2017, and upgraded your database compatibility level to the native 140.
You notice all your workload runs better on CE140, apart from this one specific query that has regressed.
In this scenario, you only have this one query that apparently does better in SQL Server 2014 than 2017. That’s all “New CE” – there’s no CE70 vs CE 120+ at issue here. Using any known trace flag, the FORCE_
_CARDINALITY_ESTIMATION hint or the FORCE_
_CARDINALITY_ESTIMATION hint doesn’t help. Rewriting the query is an option, but in the interim, I need a quick fix. How?
SQL Server 2017 CU10
, we have introduced a few new USE HINTs: the
is a supported database compatibility level. This forces the query optimizer behavior at a query level, as if the query was compiled with database compatibility level. You can refer to
for a list of currently supported values for
So to be clear, the new hint is not forcing only a specific CE model, it’s forcing the equivalent of the specific database compatibility level’s query optimizer behavior, including any query optimizer fixes that are enabled by default in that database compatibility level.
Note: if you are running with trace flag 4199 enabled globally, then *all* query optimizer fixes are enabled, not only those default to the specified database compatibility level.
And what is the outcome for the scenario above?
See for yourself below: using the new hint, I was able to compile that specific query as if the database compatibility level remained at SQL Server 2014 level (top plan), and you see the differences with the SQL Server 2017 plan (bottom plan).
Looking in the properties, also notice the differences between the used CE versions (120 vs 130) and the respective QueryTimeStats.
Like any hint, it forces certain behaviors that may get addressed in subsequent updates. So Microsoft recommends you only apply hints when no other option exists, and plan to revisit hinted code with every new upgrade. By forcing behaviors, you may be precluding your workload from benefiting of enhancements introduced in newer versions.