{"id":1182,"date":"2025-08-29T09:00:00","date_gmt":"2025-08-29T14:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1182"},"modified":"2025-08-28T22:24:01","modified_gmt":"2025-08-29T03:24:01","slug":"sql-server-2022-legacy-ce-bad-idea","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1182","title":{"rendered":"Why staying on the legacy Cardinality Estimator in SQL Server 2022 is a bad idea"},"content":{"rendered":"

Introduction<\/h2>\n

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\u2014commonly called the legacy CE\u2014dates 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.<\/p>\n

A quick refresher: what \u201clegacy CE\u201d actually means<\/h2>\n

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\u2019s skewed and correlated datasets.<\/p>\n

SQL Server 2014 introduced a redesigned CE (versions 120+) that changed key assumptions\u2014most 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.<\/p>\n

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<\/code> plan property.<\/p>\n

What changed in SQL Server 2022<\/h2>\n

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.<\/p>\n

In short: CE feedback helps the optimizer \u201clearn\u201d better estimates over time for repeating problem queries. Sticking to legacy CE prevents most of that benefit.<\/p><\/blockquote>\n

Why staying on legacy CE in SQL Server 2022 is a bad idea<\/h2>\n

1) You opt out of 2022\u2019s self-healing capabilities<\/h3>\n

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.<\/p>\n

2) You cement outdated assumptions that misestimate on modern data<\/h3>\n

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.<\/p>\n

3) You accumulate technical debt that compounds across upgrades<\/h3>\n

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.<\/p>\n

4) You introduce operational fragility (and confusion)<\/h3>\n

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.<\/p>\n

5) You deny most queries a better default<\/h3>\n

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\u2014not to anchor the entire database to the legacy model.<\/p>\n

6) You spend engineering time fighting the optimizer<\/h3>\n

Teams on legacy CE often compensate with brittle hints, extra indexes, or hand-tuned plan shapes that mask estimation errors. That d\u00e9tour 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.<\/p>\n

7) You complicate environment parity and supportability<\/h3>\n

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.<\/p>\n

\u201cBut I can still use modern features with legacy CE, right?\u201d<\/h2>\n

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\u2019s ability to help. You also keep living with legacy CE\u2019s characteristic memory grants and join choices, which are frequent root causes of spills and regressions.<\/p>\n

A practical, low-risk path off legacy CE<\/h2>\n
    \n
  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.<\/li>\n
  2. Keep modern CE by default in production once validated.<\/li>\n
  3. Surgically mitigate regressors using precise levers:\n