Azure SQL Database compatibility level 140 to 150 transition and cardinality estimator behavior

Plan Cache Behavior During Compatibility Level Updates

Increasing the compatibility level from 140 to 150 in Azure SQL Database invalidates existing cached query plans. The engine does not continue to reuse plans generated under level 140; instead, it triggers a recompilation of queries as they are executed under the new level. This ensures that the optimizer can apply the SQL Server 2019 Cardinality Estimator (CE) and Intelligent Query Processing (IQP) features immediately.

The Transition Mechanism

Because the compatibility level is a database-scoped setting, the change acts as a global signal to the engine that the rules for plan generation have changed. You do not need to manually clear the cache (e.g., using DBCC FREEPROCCACHE) to ensure the new CE model is applied, as the version mismatch between the cached plan and the current database level naturally triggers a recompile.

Likely Performance Impacts

While the invalidation is automatic, the transition typically introduces two primary risks:

• CPU Spikes: A temporary increase in CPU utilization is common immediately following the update due to the simultaneous recompilation of many high-frequency queries.
• Plan Regression: The new CE may produce different row estimates for filtered aggregates or parameter-sensitive predicates, which can lead to suboptimal plan selection compared to level 140.

Recommended Transition Steps

1. Enable Query Store: Ensure Query Store is active before the transition to capture a baseline of "known-good" plans from level 140.
2. Update Compatibility Level: Execute the level change:

   ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 150;

3. Monitor and Force: Use the Query Store "Regressed Queries" report to identify performance drops. If a specific query regresses, force the previous plan from level 140.
4. Granular Correction: For queries that still struggle under the new CE, test the legacy estimator specifically for that query using a hint:

   SELECT ... OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Diagnostic Requirement

To provide a more tailored recommendation regarding plan stability, please specify if you are currently using Automatic Tuning (Force Last Good Plan), as this can automate the mitigation of the regressions described above.