Why staying on the legacy Cardinality Estimator in SQL Server 2022 is a bad idea

Posted on by Stephen Planck

Introduction

Cardinality estimation (CE) is how the optimizer predicts the number of rows that will flow through each operator in a plan. Those estimates drive cost, join choices, memory grants, and ultimately latency and resource usage. SQL Server has shipped multiple CE models over time. The pre-2014 model—commonly called the legacy CE—dates back to SQL Server 7.0. Starting in SQL Server 2014, Microsoft introduced a new CE and has continued refining it in later releases, including SQL Server 2022. Keeping the legacy CE turned on in SQL Server 2022 is usually the wrong long-term choice.

A quick refresher: what “legacy CE” actually means

The legacy CE (version 70) is built on four simplifying assumptions: independence (filters on different columns are independent), uniformity (values are evenly distributed), simple containment (joins assume only matching values exist), and inclusion (filtered constants exist in the histogram). These assumptions were reasonable for older, simpler workloads, but they are often inaccurate on today’s skewed and correlated datasets.

SQL Server 2014 introduced a redesigned CE (versions 120+) that changed key assumptions—most notably modeling correlation across predicates and using base containment for joins. The goal is more realistic estimates across a broader range of modern OLTP and analytics workloads.

You can still force the legacy model globally or per database, and you can force it per query. Execution plans expose the CE model in the CardinalityEstimationModelVersion plan property.

What changed in SQL Server 2022

SQL Server 2022 (compatibility level 160) adds Cardinality Estimation feedback as part of Intelligent Query Processing. The engine can observe large estimate errors on repeating queries and automatically adjust estimation behavior for those queries, persisting corrections via Query Store so future executions get better estimates. CE feedback is designed to operate with the modern CE pipeline; if you compile queries under the legacy CE globally, you reduce or eliminate the benefit of this feature on those queries.

In short: CE feedback helps the optimizer “learn” better estimates over time for repeating problem queries. Sticking to legacy CE prevents most of that benefit.

Why staying on legacy CE in SQL Server 2022 is a bad idea

1) You opt out of 2022’s self-healing capabilities

CE feedback is a 2022 feature that mitigates bad estimates by adjusting model behavior per query. Running the legacy CE globally means fewer queries can take advantage of that automatic correction, leaving more persistent misestimates and more manual triage for you.

2) You cement outdated assumptions that misestimate on modern data

Independence and uniformity assumptions often mispredict row counts for multi-predicate filters, skewed distributions, and heterogeneous join keys. Misestimates cascade into poor join choices, over/under memory grants, spills, unnecessary scans, and under- or over-parallelization.

3) You accumulate technical debt that compounds across upgrades

The longer you run on legacy CE, the more code, indexes, and operational habits accrete around those estimates. When you finally move to the current CE, you face a bigger behavioral delta, more regression risk, and a larger validation effort. Deferring only increases the blast radius later.

4) You introduce operational fragility (and confusion)

Instance or database toggles (and trace flags) create hidden state. Changing CE settings does not retroactively recompile every cached plan; mixed caches are common during transitions. That leads to inconsistent performance and hard-to-explain plan differences until caches stabilize or you force recompiles.

5) You deny most queries a better default

The modern CE is the default for a reason: across broad workloads it improves average plan quality. A small subset of queries can regress, but the right response is to keep the modern CE globally and target the exceptions with per-query mitigations—not to anchor the entire database to the legacy model.

6) You spend engineering time fighting the optimizer

Teams on legacy CE often compensate with brittle hints, extra indexes, or hand-tuned plan shapes that mask estimation errors. That détour increases maintenance, hurts concurrency, and can become invalid as data evolves. Let the modern CE and IQP features do their work; reserve manual interventions for true edge cases.

7) You complicate environment parity and supportability

Different CE settings across dev, test, and prod make reproduction and root cause analysis harder. Many vendor and platform troubleshooting guides assume modern CE behavior; staying on legacy CE increases time-to-resolution when performance issues arise.

“But I can still use modern features with legacy CE, right?”

Partly. If you run at compatibility level 160 and only flip legacy CE on, you still retain many optimizer and IQP features tied to compatibility level. However, you continue to inherit legacy estimation patterns by default, and you undercut CE feedback’s ability to help. You also keep living with legacy CE’s characteristic memory grants and join choices, which are frequent root causes of spills and regressions.

A practical, low-risk path off legacy CE

  1. Use the highest compatibility level and disable legacy CE in a safe environment (pre-prod). Measure top statements with Query Store before and after.
  2. Keep modern CE by default in production once validated.
  3. Surgically mitigate regressors using precise levers:
    • Per-query hint to force legacy or default CE on a single statement.
    • Query Store hints to persist the same effect without changing code.
    • QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n to emulate an older optimizer for one query, when required.
  4. Let SQL Server 2022’s CE feedback work on the rest of the workload so repeated queries with large estimate errors self-correct over time.

Additional points that are easy to overlook

  • Plan provenance matters. During transitions, check the plan property CardinalityEstimationModelVersion to confirm which CE compiled each plan.
  • Prefer database-scoped configs and Query Store hints over trace flags. They’re easier to reason about and safer for long-term maintainability.
  • CE continues to evolve. Estimation improvements and optimizer behaviors accumulate with compatibility levels (130, 140, 150, 160). Staying on legacy CE means you miss those cumulative gains.
  • Avoid hidden drift between environments. Standardize on modern CE globally and document any query-level exceptions.
  • Recompilation strategy matters. When changing CE settings, plan for controlled recompiles (or cache clears) to avoid long periods of mixed behavior.

Conclusion

The legacy CE is a valuable temporary escape hatch during upgrades—but a poor long-term strategy on SQL Server 2022. It locks you into outdated assumptions, prevents the engine from correcting estimate errors automatically, increases operational complexity, and builds technical debt that becomes more expensive to unwind later. Keep the modern CE on by default; use Query Store and per-query hints to isolate the few regressors. That approach yields better average plan quality today and a smoother upgrade path tomorrow.

References:

Leave a Reply

©2025 SQL Table Talk
Disclaimer: The views and opinions expressed on this blog are solely my own and do not reflect the views and opinions of Microsoft Corporation. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.