SQL Server Compatibility Level: What It Changes and How to Raise It Safely

Every SQL Server database has a compatibility level, a number such as 150, 160 or 170. It doesn’t decide which SQL Server version the database runs on. It decides which query optimizer and T-SQL behavior the database gets from the engine it’s running on. A database at level 130 on SQL Server 2025 runs on the 2025 engine but plans its queries largely like SQL Server 2016.

That’s why the compatibility level is the most important setting in any upgrade. Raising it unlocks years of optimizer improvements, and it’s also the step where queries that ran fine yesterday can suddenly choose a much worse plan. This guide explains what the level controls, what changes at 150, 160 and 170, what happens to the plan cache when you change it, and the Query Store workflow to raise it without performance surprises.

Compatibility Levels by SQL Server Version

Three rules follow from this table:

• New databases get the engine’s default level, unless the model database is set lower.
• A database restored or attached from an older version keeps its level, as long as the new engine supports it. Upgrading the engine alone doesn’t change query behavior.
• If the old level isn’t supported, the database is raised automatically to the lowest supported level. A level-90 database restored on SQL Server 2025 becomes 100.

-- Every database's level next to the engine's default
SELECT
    d.name,
    d.compatibility_level,
    (SELECT compatibility_level FROM sys.databases WHERE name = N'model') AS model_level,
    CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) * 10              AS engine_default
FROM sys.databases AS d
ORDER BY d.compatibility_level, d.name;

What Compatibility Level Actually Controls

Microsoft ties two kinds of plan-affecting change to the default level of each new version:

• Cardinality estimator (CE) changes. The CE predicts how many rows each step of a plan returns, and those estimates drive join types, memory grants and parallelism. CE changes in a new version are enabled only at that version’s default level. Level 120 introduced the “new” CE in SQL Server 2014; every version since has refined it.
• Query optimizer fixes. Optimizer hotfixes released for earlier versions are enabled automatically at the newer default level. At older levels they stay off unless you enable them with trace flag 4199 or the QUERY_OPTIMIZER_HOTFIXES database scoped configuration.

On top of that, most intelligent query processing features are gated by level:

Compatibility level also protects some T-SQL behavior. For example, from level 130 the implicit conversion from datetime to datetime2 accounts for fractional milliseconds and returns different values than at level 120. What it doesn’t protect is discontinued functionality: a feature removed from the engine doesn’t come back at a lower level.

Why Raising the Level Can Make Queries Slower

Most queries get the same or a better plan under a newer CE and optimizer. A few don’t. A query that depended on an old estimate, a statistic that happened to work, or a plan shape the new model no longer chooses can regress from milliseconds to minutes.

The typical symptoms after a compatibility level change are a handful of queries with much higher CPU or duration, sudden large memory grants or spills, and parallel plans where there were none. The danger is that these few queries are often the ones that matter most, such as the month-end report or the busiest stored procedure.

What Happens to the Plan Cache

Changing the compatibility level invalidates the plan cache for that database, the same way many other ALTER DATABASE operations do. Every query and stored procedure in the database compiles again on its next execution, now under the new level’s rules.

In practice:

• Expect a burst of compilations and higher CPU right after the change. On a busy OLTP system, make the change in a quiet period.
• You’ll see the plan cache for that database almost empty immediately afterwards. That’s expected, not a sign of a problem.
• Plans don’t change “slowly over time”; they change on the first execution after the switch. Watch the first hour closely.

-- Cached plans per database: run before and after the change
SELECT
    DB_NAME(CONVERT(int, pa.value)) AS database_name,
    COUNT(*)                        AS cached_plans,
    SUM(cp.size_in_bytes) / 1024    AS size_kb
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE pa.attribute = N'dbid'
GROUP BY pa.value
ORDER BY cached_plans DESC;

The Safe Upgrade Workflow with Query Store

Microsoft’s recommended approach uses Query Store to make the change measurable and reversible at query level:

1. Upgrade the engine, keep the old level

Move the database to the new SQL Server version at its existing compatibility level. The application should behave as before. This separates “new engine” problems from “new optimizer” problems.

2. Turn on Query Store and collect a baseline

ALTER DATABASE SalesDB SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 2048,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

Let it run through a full business cycle, including month-end or other peak jobs, so the baseline contains the queries that matter. Our Query Store best practices cover sizing and capture settings.

3. Raise the compatibility level

ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = 170;

4. Find and fix regressions

Use the Regressed Queries report in SSMS, or compare runtime statistics before and after the change in Query Store. For each regressed query you have three quick options while you work on the root cause:

-- Force the last known good plan
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 902;

-- Or keep the new level but run one query with the old optimizer behavior,
-- without changing application code
EXEC sys.sp_query_store_set_hints
     @query_id = 4711,
     @query_hints = N'OPTION (USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

-- Or fall back to the legacy cardinality estimator for one query
EXEC sys.sp_query_store_set_hints
     @query_id = 4711,
     @query_hints = N'OPTION (USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

5. Remove the workarounds over time

Forced plans and hints are a safety net, not the end state. Revisit them: fix the statistics, indexes or query patterns behind the regression, then unforce the plan.

When to Keep an Older Level

There are good reasons not to raise the level immediately:

• The application vendor certifies only a specific level. ERP and packaged software often do; check before changing anything.
• There’s no time to collect a baseline before the upgrade. Upgrade the engine first, raise the level later.
• A critical query regresses and the fix needs code changes you can’t make yet. Keep the level and plan the change.

At an older level you still get the new engine’s storage, availability and security improvements; you miss the optimizer improvements. Enabling QUERY_OPTIMIZER_HOTFIXES at database scope gives you post-release optimizer fixes without a level change.

Compatibility Level Checklist

• Current level of every database is documented, together with the engine version.
• Vendor-certified levels are confirmed for packaged applications.
• The engine upgrade and the level change are separate steps.
• Query Store is on and has a baseline covering a full business cycle.
• The level change is made in a quiet period, with the compile spike expected.
• Regressed queries are fixed with forced plans or Query Store hints, not by reverting the database.
• Forced plans and hints are reviewed and removed once root causes are fixed.