4

I am running SQL Server 2019 RTM CU18 I am in the process of testing workloads against this server before migrating to it. All databases are set to SQL 2019 compatibility level.

I am finding that using the database scoped config legacy_cardinality_estimation = on seems to make a lot of queries run faster.

Anyway, I have noticed something strange. I have enabled legacy_cardinality_estimation but there is a particular query that most of the the time still uses CardinalityEstimationModelVersion 150 instead of 70 like it's supposed to.

Sometimes it will run with CardinalityEstimationModelVersion 70 and other times 150.

Why is this? Under which conditions would the same query choose to use CardinalityEstimationModelVersion 150 even though the DB has been specifically set with legacy_cardinality_estimation = on.

The query is part of a stored procedure that is built dynamically. Also, there is a recompile hint on the query.

Any ideas?

Thanks

Further Investigations:

I have turned off legacy cardinality estimation and changed the database to compatibility level 110. The same thing happens With certain executions of this query, it runs with 150 CE

What I have noticed is that when it chooses to run with 150 CE - This is when no rows are returned by the query

enter image description here

The first 2 rows are executions with 150 CE

The bottom 2 rows are executions with Legacy CE

Also, with the 150 CE executions - There is an Implicit Conversion warning in the plan

Not sure if all these things are clues to what is going on here.

More Investigations:

DatabaseContextSettingsId="8" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="14.2801" StatementEstRows="2777.06" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7E6FC3AE7781DFB0" QueryPlanHash="0x5528C4469CB66921" CardinalityEstimationModelVersion="70">

DatabaseContextSettingsId="8" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.0295867" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7E6FC3AE7781DFB0" QueryPlanHash="0xAA261B593A12FBD4" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150">

At this point, I am just trying to understand why or under which conditions the optimizer would change the CE model.

Furthermore:

The statement joins onto several views all of which reference DB's that are set with legacy cardinality estimation = on

There is a cross apply to an Inline TVF the contents of which also reference DB's with legacy cardinality estimation = on

Improve this question
3
  • Thanks. You mentioned Query Store. There's no forced plans or Query Store Hints being applied to this query, right?
    – J.D.
    Commented Sep 6, 2023 at 12:42
  • Nope - Nothing like that
    – Sicilian-Najdorf
    Commented Sep 6, 2023 at 12:46
  • There's a temp table in the proc but outside of this statement and TempDB is CL150. What is the best course of action for changing compatibility level or using legacy cardinality - Change EVERYTHING including system DB's?
    – Sicilian-Najdorf
    Commented Sep 8, 2023 at 6:38

1 Answer 1

Reset to default
2

There are cases of execution plans being re-used if people start using Trace Flags as explained by Martin Smith in his answer to the question Will setting cardinality estimator trace flag 9481 on/off automatically recompile affected plans? (Stackoverflow | Answer).

Running the following with "Actual execution plan enabled" the first 2 plans have CardinalityEstimationModelVersion="150" in the XML and the 20 row estimate and the second 2 plans CardinalityEstimationModelVersion="70" and the 9 row estimate.

This shows that after the trace flag is changed the execution plans from cache can still be reused.

So changing Trace Flags can affect the CardinalityEstimationModelVersion being used.

The documentation for TF 9481 for example, lists the following:

(emphasis mine)

Sets the Query Optimizer cardinality estimation (CE) model to SQL Server 2012 (11.x) and earlier (version 70), irrespective of the compatibility level of the database. For more information, see KB2801413.

Starting with SQL Server 2016 (13.x), to accomplish this at the database level, see the LEGACY_CARDINALITY_ESTIMATION option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 (13.x) Service Pack 1, to accomplish this at the query level, add the USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION' query hint instead of using this trace flag.

Scope: Global or session or query (QUERYTRACEON).

Reference: DBCC TRACEON - Trace Flags (Transact-SQL) (Microsoft Learn)

In summary there are multiple ways to change the CardinalityEstimationModelVersion being used.

I just received a gentle reminder that all my previously suggested solutions are obsolete, as any setting, be it trace flag or HINT, would show up in the execution plan (that were deleted from the question). How could I forget that....?

In the end there are only two possible suggestions.

  1. Open up an SR with Microsoft.
  2. File a bug SQL (Microsoft Azure | Share your Ideas)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.