Developer’s Choice: Hinting Query Execution model

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 https://aka.ms/traceflags. 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 
FROM tbl1
OPTION (QUERYTRACEON 4138);

SELECT TOP 1000 col1 
FROM tbl1
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

You can read more about USE HINTS here and here, and on row goal here.

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_LEGACY_CARDINALITY_ESTIMATION and FORCE_DEFAULT_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 here.

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 scenario:

1. You have upgraded your SQL Server from 2014 to SQL Server 2017, and upgraded your database compatibility level to the native 140.
2. You notice all your workload runs better on CE140, apart from this one specific query that has regressed.
3. And because you haven’t run the recommended upgrade process, you don’t have a previously known-good plan in Query Store, and Automatic Tuning cannot kick in and help.

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_LEGACY_CARDINALITY_ESTIMATION hint or the FORCE_DEFAULT_CARDINALITY_ESTIMATION hint doesn’t help. Rewriting the query is an option, but in the interim, I need a quick fix. How?

In SQL Server 2017 CU10, we have introduced a few new USE HINTs: the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n, where n 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 sys.dm_exec_valid_use_hints for a list of currently supported values for n.

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.

Pedro Lopes (@SQLPedro) – Senior Program Manager