Compatibility Level vs. Cardinality Estimator in SQL Server: A Complete Guide

If you’ve spent any time tuning SQL Server performance, you’ve almost certainly run into two terms that get confused constantly: Compatibility Level (CL) and Cardinality Estimator (CE). They sound like they might be the same thing since changing one often changes the other’s behavior. But they are two distinct engine concepts, and understanding where they overlap and where they diverge is essential for anyone doing upgrades, migrations, or query tuning.

This article breaks down what each one actually is, how they relate to each other, what changed and when, and how to diagnose and fix problems caused by CE-related regressions.

What Is Database Compatibility Level?

Compatibility Level is a database-scoped setting that tells the SQL Server query processor which version-specific behaviors to emulate for that database. It’s set per database (not per instance), which means a single SQL Server instance can host databases running at different compatibility levels simultaneously.

You can check and change it easily:

-- Check current compatibility level
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Change compatibility level
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 160;

Compatibility Level Numbers by SQL Server Version

SQL Server Version	Compatibility Level
SQL Server 2008	100
SQL Server 2012	110
SQL Server 2014	120
SQL Server 2016	130
SQL Server 2017	140
SQL Server 2019	150
SQL Server 2022	160

The number is simply the SQL Server version number times 10 (with some historical exceptions from earlier versions).

What Compatibility Level Actually Controls

Compatibility level primarily governs T-SQL surface-area behaviors and syntax semantics things like:

• Whether certain deprecated syntax still works or throws an error
• Behavior of specific functions (e.g., date/time parsing edge cases)
• Which query optimizer features are available (not just CE but things like batch mode on rowstore, Intelligent Query Processing features, certain hints)
• Certain implicit conversion and comparison rules

Crucially, compatibility level does not change the physical version of the SQL Server engine or the database file format. You can run a database at compatibility level 110 on a SQL Server 2022 instance. The engine binaries are 2022, but many T-SQL behaviors will act like SQL Server 2012. This is exactly why compatibility level is the go-to tool for minimizing breaking changes during an upgrade: you upgrade the instance first, keep the database at its old compatibility level, and then ratchet the level up gradually once you’ve validated things.

Example: Compatibility-Level-Gated Feature

Intelligent Query Processing (IQP) features are a good real-world example. Features like Adaptive Joins, Interleaved Execution for multi-statement table-valued functions, and Memory Grant Feedback are only available once your database compatibility level is at or above the level in which they were introduced even though the underlying SQL Server engine fully supports them.

-- On SQL Server 2022, this database won't get most IQP features
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 130;

-- Bumping it up unlocks 2017+ and 2019+ IQP features
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 150;

What Is the Cardinality Estimator?

The Cardinality Estimator (CE) is the component of the SQL Server Query Optimizer responsible for estimating how many rows will be returned by each operator in a query plan, a scan, a join, a filter, an aggregation, and so on.

These row-count estimates are arguably the single most influential input into the optimizer’s decision-making. Based on estimated cardinality, the optimizer decides:

• Whether to use a Nested Loop Join, Hash Join, or Merge Join
• Whether to use a Seek or a Scan
• How much memory to grant for sort/hash operations
• Whether to parallelize a query, and to what degree (DOP)
• The order in which to join tables

If the CE’s estimates are close to reality, the optimizer tends to make good choices. If the estimates are badly wrong, say, it estimates 10 rows will come back but 10 million actually do, so, you get spilled hash joins, undersized memory grants, serial execution where parallelism was needed, or nested loop joins driving into a huge table millions of times. This is one of the most common root causes of “the query used to run fine and now it’s slow.”

You can see the CE’s estimates versus actual row counts directly in an execution plan:

SET STATISTICS XML ON;
GO
SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2026-01-01';
GO
SET STATISTICS XML OFF;

In the resulting plan, hover over any operator and compare Estimated Number of Rows to Actual Number of Rows (or use SET STATISTICS PROFILE ON / the “Actual Execution Plan” view in SSMS). A large divergence between the two is the classic symptom of a cardinality estimation problem.

Legacy CE vs. New CE

Here’s where most of the confusion and most of the real-world pain comes from.

The Legacy Cardinality Estimator (CE version 70)

From SQL Server 7.0 all the way through SQL Server 2012, SQL Server used the same fundamental cardinality estimation model, internally referred to as CE version 70. It had accumulated known weaknesses over nearly two decades:

• Independence assumption: it assumed that filters on different columns of the same table were statistically independent of each other, which is frequently false in real data (e.g., City = 'Delhi' and State = 'Delhi' are highly correlated, not independent).
• Estimation problems with ascending key columns (e.g., identity columns, date columns where new rows are always higher than the max value in statistics), since values beyond the max in the histogram were poorly estimated.
• Weak handling of multi-column predicates, joins with complex filter combinations, and correlated columns generally.

The New Cardinality Estimator (CE version 120+)

With SQL Server 2014, Microsoft shipped a substantially rewritten cardinality estimator, the first major overhaul in nearly 20 years. It changed several of the fundamental assumptions:

• Exponential backoff for combining multiple predicate selectivities instead of pure independence, which usually produces more realistic estimates when filters correlate.
• Better handling of join estimation, particularly for ascending/growing key scenarios.
• Different statistical algorithms for string predicates, “no matching statistics” scenarios, and distinct value counts.

The new CE is not just a bug-fixed version of the old one but it’s a different statistical model. That’s exactly why it’s dangerous: for a huge number of queries, the new model gives better estimates and better plans. But for a meaningful minority of queries, especially in older, heavily-tuned databases where indexes and query patterns were built around the old model’s quirks, the new CE can produce worse plans than before, sometimes dramatically worse. This became one of the most notorious “gotchas” of the SQL Server 2014 upgrade cycle: organizations would upgrade, everything looked fine in testing, and then specific production queries would regress badly under load.

How CE Version Is Tied to Compatibility Level

This is the crux of the relationship between the two concepts. Which CE model a query uses is determined by the database’s compatibility level (with some override options, covered below):

Compatibility Level	CE Version Used
≤ 110 (SQL Server 2012 and earlier)	Legacy CE (CE 70)
120 (SQL Server 2014)	New CE (2014 model)
130 (SQL Server 2016)	New CE (2016 refinement)
140 (SQL Server 2017)	New CE (2017 refinement)
150 (SQL Server 2019)	New CE (2019 refinement)
160 (SQL Server 2022)	New CE (2022 refinement)

So when you raise a database’s compatibility level from, say, 110 to 150 as part of a modernization project, you are simultaneously opting that database into a completely different cardinality estimation model, plus every other syntax/behavior change tied to that compatibility level jump. This is why “just bump the compatibility level” upgrades sometimes cause unexpected plan regressions. The CE switch is a silent passenger riding along with the compatibility level change.

Important nuance since SQL Server 2016: Microsoft made minor CE refinements within the “new CE” family at each version bump (130, 140, 150, 160 each behave slightly differently from each other), not just a single monolithic “new CE” frozen at the 2014 model. So compatibility level 130 and compatibility level 150 are both “new CE,” but they are not byte-for-byte identical in every estimation scenario.

Decoupling CE from Compatibility Level

Recognizing that forcing a CE change bundled with every other compatibility-level behavior change was risky, Microsoft gave DBAs a way to control the CE independently of compatibility level, starting with SQL Server 2016 SP1.

Method 1: Database-Scoped Configuration (Recommended, SQL Server 2016+)

-- Force legacy CE regardless of compatibility level
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Revert to using whatever CE matches the compatibility level
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

-- Check current setting
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

This is powerful because it lets you take advantage of all the newer compatibility level’s syntax and IQP features while still running the legacy CE model for cardinality estimation specifically, useful as a stopgap while you investigate and fix specific regressed queries.

Method 2: Trace Flags (Instance-wide or Query-scoped)

-- Force new CE at the session or query level
SELECT * FROM Orders OPTION (QUERYTRACEON 2312);

-- Force legacy CE at the session or query level
SELECT * FROM Orders OPTION (QUERYTRACEON 9481);

• Trace Flag 2312 forces the new CE model.
• Trace Flag 9481 forces the legacy CE model.

These can also be enabled instance-wide as startup trace flags, but query-level or database-scoped control is almost always the better, more surgical approach in modern SQL Server versions.

Method 3: Query-Level Hint (Alternative Syntax)

SELECT * FROM Orders
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

This achieves the same effect as trace flag 9481 but uses the more modern USE HINT syntax introduced in SQL Server 2016 SP1, which doesn’t require sysadmin-level trace flag knowledge and is more discoverable/self-documenting in query text.

Worked example to see the difference

Here’s a simplified illustration of how compatibility level changes can shift plan shape purely through the CE, holding the query itself constant.

-- Scenario: correlated columns, legacy CE assumes independence
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    Department VARCHAR(50),
    JobTitle VARCHAR(50),
    Country VARCHAR(50)
);
-- Assume Department = 'Engineering' and JobTitle = 'Software Engineer'
-- are highly correlated (most Software Engineers ARE in Engineering)

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
SELECT * FROM dbo.Employees
WHERE Department = 'Engineering' AND JobTitle = 'Software Engineer';
-- Legacy CE: multiplies selectivity of each predicate independently,
-- tends to UNDER-estimate rows for correlated predicates like this

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
SELECT * FROM dbo.Employees
WHERE Department = 'Engineering' AND JobTitle = 'Software Engineer';
-- New CE: applies exponential backoff instead of pure multiplication,
-- typically produces a HIGHER, more realistic row estimate here

In a real environment with millions of rows, that estimation difference can be the deciding factor between the optimizer choosing a cheap nested loop (correct when the estimate says “10 rows”) versus a hash join with a larger memory grant (correct when the actual number is “50,000 rows”). Get the estimate wrong in either direction and you pay for it, either through excessive loop iterations or through memory grant spills to tempdb.

Practical Guidance

A few field-tested principles when you’re dealing with CL/CE together:

1. Never bump compatibility level blindly during an upgrade. Treat it as a distinct, tested change & not an automatic side effect of moving to new hardware or a new SQL Server version. Upgrade the instance, leave compatibility level where it was, validate, then raise it deliberately (often in a lower environment first, under representative load).
2. Use Query Store before and during any compatibility level change. Query Store lets you capture plan and runtime metrics before the change, then compare regressed queries after the change, and even force the old (or new) plan back via sp_query_store_force_plan while you investigate.
3. If you see a regression after raising compatibility level, isolate whether it’s the CE or something else. Use LEGACY_CARDINALITY_ESTIMATION at the database scope, or OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) at the query scope, to test whether reverting just the CE (while keeping the new compatibility level’s other features) fixes the regression. If it does, you’ve confirmed the CE is the culprit and can decide between a targeted hint, an updated/filtered statistic, or a permanent legacy CE override for that database.
4. Keep statistics current regardless of which CE you use. Neither CE model can compensate for stale or missing statistics. UPDATE STATISTICS and appropriate auto-update/auto-create statistics settings matter more than which CE version you’re on.
5. Azure SQL Database automatically uses the newest Cardinality Estimator (CE) version that matches its compatibility level. Microsoft sometimes tries out new CE improvements in Azure SQL Database first, before rolling them out to the regular on-premises version of SQL Server. If you’re managing Azure SQL Database, you can still check and set the compatibility level. Just run the same sys.databases query you’d use on a regular SQL Server. This works even though Azure keeps the underlying engine constantly updated behind the scenes.

Summary

	Compatibility Level	Cardinality Estimator
Scope	Per database	Determined by compatibility level (or overridden independently)
Controls	T-SQL syntax/behavior emulation, availability of optimizer features (e.g., IQP)	Row-count estimation logic used to build query plans
Introduced changes	Every SQL Server version	Major rewrite in SQL Server 2014 (CE 120); refined further in 2016, 2017, 2019, 2022
Can be changed independently?	Yes, via ALTER DATABASE ... SET COMPATIBILITY_LEVEL	Yes, since SQL Server 2016 SP1, via LEGACY_CARDINALITY_ESTIMATION database-scoped config, trace flags 2312/9481, or USE HINT

Compatibility level works like a master switch. Cardinality Estimator (CE) is just one of several things that switch turns on or off. For a long time, these two always changed together, which is why people often think they’re the same thing.

But since SQL Server 2016 SP1, that’s no longer true. You can change the Cardinality Estimator on its own, without touching anything else that compatibility level controls.

Why does this matter? Because the Cardinality Estimator changed in a major way back in 2014. If you understand that the CE and compatibility level are separate, and that the CE had this big change, you can often fix a performance problem after an upgrade in just five minutes. Without that knowledge, the same problem can turn into a multi-day struggle.