You upgrade SQL Server, test a few things and declare it a success. Six months later someone asks why the query optimizer still isn’t using the newer cardinality estimator. Turns out the database has been sitting at compatibility level 130 since 2016. The server was upgraded but not the database.
That’s the thing about compatibility level: it follows the database, not the server. When you restore or attach a database to a newer SQL Server instance, it keeps whatever compatibility level it had before.
What it actually controls
Compatibility level isn’t just a version label. It controls which version of the query optimizer is used, how certain T-SQL features behave, and which newer engine features are available.
The cardinality estimator changed significantly at level 120 (SQL Server 2014). The new CE makes different assumptions about data distribution and join estimation. For some queries it’s better, but for a few it’s worse. Microsoft couldn’t just move everyone to it on upgrade, so they tied it to compatibility level.
Batch mode processing for rowstore tables came in at level 150. Intelligent query processing features like adaptive joins and memory grant feedback are tied to 150 and above. If your database is sitting at 130, you’re not getting any of that, even on SQL Server 2019 or 2022.
Checking where you are
SELECT name, compatibility_level
FROM sys.databases
ORDER BY name;SQLThe number maps to a SQL Server version: 100 is 2008, 110 is 2012, 120 is 2014, 130 is 2016, 140 is 2017, 150 is 2019, 160 is 2022, 170 is 2025.
It’s not unusual to find production databases on a SQL Server 2022 instance sitting at 130 because that’s what they were when they were migrated years ago and nobody got round to changing it.
Why people leave it alone
Changing compatibility level can affect query plans. The cardinality estimator change is the main one. A query that runs fine at 130 might get a different plan at 160, and if the statistics are stale or the data distribution is unusual, that different plan might be slower.
Before bumping the level on a production database, it’s worth running the workload through Query Store first. Enable Query Store, let it collect a baseline, change the compatibility level, then watch for plan regressions. Query Store’s Query Tuning Assistant in SSMS can help with this.
-- Enable Query Store before making the change
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO
);SQLLet it run for a week or two under normal load, then make the change and watch for regressions.
Changing it
When you’re ready:
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 160;SQLNo restart needed, no downtime. The change takes effect for new connections immediately.
If you hit a regression you can roll it back the same way. Having Query Store running means you can also force the old plan while you investigate, without reverting the compatibility level.
The broader point
SQL Server version and database compatibility level aren’t the same thing. The version is what the instance is running. The level is what features and optimizer behaviour each database gets. You can have a SQL Server 2025 instance with databases at five different compatibility levels across it, and that’s not unusual after years of migrations and upgrades.
If you’ve done a major version upgrade recently and haven’t looked at compatibility levels, it’s worth a few minutes. You may not be making use of the new enhancements.