Azure SQL Database compatibility level 140 to 150 transition and cardinality estimator behavior
23.5K reputation · 16 Aug 2022, 00:42 UTC
Query Plan Stability During CE Migration
Updating the database compatibility level from 140 to 150 in Azure SQL Database triggers a shift from the legacy cardinality estimator (CE) to the new model. This change affects how the engine calculates estimated row counts for queries involving filtered aggregates and parameter-sensitive predicates.
A primary concern during this transition is the interaction between the plan cache and the new CE logic. Because compatibility levels are database-scoped, the transition applies to all schemas simultaneously. There is uncertainty regarding how cached plans generated under level 140 behave when the database is promoted to level 150, particularly when automatic tuning features like Intelligent Indexing are active.
- How does the engine handle the reuse of existing cached plans after the compatibility level is increased to 150?
- Does the transition to the new CE model automatically invalidate all cached plans, or is a manual cache clear required to ensure new estimates are applied?
