Reading Older SQL Servers: Engine Evolution from 2016 to 2025 and a Compatibility Level Upgrade Strategy

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10; the latest CU (Cumulative Update) in Microsoft's update list as of October 2026); for the comparisons the same database's compatibility level was switched between 130 and 170. The lifecycle dates come from the Microsoft Lifecycle pages.

Guess First: The Same Query at Five Compatibility Levels

You have a server with SQL Server 2025 installed. An old application's database runs on it, and its compatibility level has never been changed. On the same engine, with the same data, changing only the database's compatibility level, we measured five things: a table variable's row estimate, a multi-statement TVF's (MSTVF, Table-Valued Function) row estimate, the execution mode of an aggregate over 2.5 million rows, how many times a scalar function is called, and whether REGEXP_LIKE works:

DECLARE @CustomerOrders TABLE (OrderId int PRIMARY KEY);
INSERT @CustomerOrders (OrderId) SELECT OrderId FROM dbo.Orders WHERE CustomerId = 2;
SELECT COUNT(*) AS lines FROM @CustomerOrders AS co JOIN dbo.OrderItems AS i ON i.OrderId = co.OrderId;
SELECT COUNT(*) AS lines FROM dbo.CustomerOrdersMulti(2) AS co JOIN dbo.OrderItems AS i ON i.OrderId = co.OrderId;
SELECT BookId, SUM(Quantity) AS sold FROM dbo.OrderItems GROUP BY BookId ORDER BY sold DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
SELECT SUM(dbo.OrderTotal(OrderId)) AS revenue FROM dbo.Orders WHERE CustomerId = 3;
SELECT COUNT(*) AS regexp_matches FROM dbo.Customers WHERE REGEXP_LIKE(Email, '^customer4[0-9]@');

Since the server is 2025, all of them should show 2025 behavior, right?

compat  table variable est  MSTVF est  aggregate mode  UDF calls  REGEXP_LIKE
130     1                   100        Row             8916       Msg 195
140     1                   17359      Row             8916       Msg 195
150     17359               17359      Batch           0          Msg 195
160     17359               17359      Batch           0          Msg 195
170     17359               17359      Batch           0          10

Msg 195, Level 15, State 10, Server b8be943222fa, Line 1
'REGEXP_LIKE' is not a recognized built-in function name.

No. The engine is 2025, but query processing behavior depends on the database's compatibility level. At 130 the table variable is estimated at 1 row and the MSTVF at 100 rows, the aggregate runs in row mode, the scalar function is called 8,916 times, and the new function isn't recognized at all. The results (row counts, the total amount) are the same at every level; what changes is the path to them. This is the last post of the series: it covers how the engine changed from 2016 to 2025, what exactly the compatibility level controls, and a plan for doing an upgrade safely with Query Store.

Engine Version and Compatibility Level

There are two separate concepts. The engine version is the installed SQL Server itself: 2025 = 17.x, 2022 = 16.x, 2019 = 15.x, 2017 = 14.x, 2016 = 13.x. The compatibility level is a per-database setting that decides which version's behavior is used to compile the queries in that database: 170, 160, 150, 140, 130. An engine supports several older levels besides its own; that's how a database coming from 2016 can keep running at level 130 on 2025.

The first places to look when reading an old server or a newly migrated database:

SELECT SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('ProductMajorVersion') AS major,
       SERVERPROPERTY('ProductUpdateLevel') AS update_level,
       SERVERPROPERTY('Edition') AS edition;
SELECT name, compatibility_level, is_query_store_on
FROM sys.databases WHERE name = DB_NAME();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN (N'LEGACY_CARDINALITY_ESTIMATION', N'QUERY_OPTIMIZER_HOTFIXES',
               N'TSQL_SCALAR_UDF_INLINING', N'BATCH_MODE_ON_ROWSTORE', N'DEFERRED_COMPILATION_TV',
               N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION', N'OPTIONAL_PARAMETER_OPTIMIZATION');
DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;

product_version|major|update_level|edition
---------------|-----|------------|-------
17.0.5035.5|17|CU10|Enterprise Developer Edition (64-bit)
name|compatibility_level|is_query_store_on
----|-------------------|-----------------
sqls_19|170|1
name|value
----|-----
LEGACY_CARDINALITY_ESTIMATION|0
QUERY_OPTIMIZER_HOTFIXES|0
TSQL_SCALAR_UDF_INLINING|1
BATCH_MODE_ON_ROWSTORE|1
DEFERRED_COMPILATION_TV|1
PARAMETER_SENSITIVE_PLAN_OPTIMIZATION|1
OPTIONAL_PARAMETER_OPTIMIZATION|1

Database scoped configurations give fine control on top of the compatibility level: the compatibility level turns a new feature on, and a setting can turn that feature off individually. LEGACY_CARDINALITY_ESTIMATION = 1 makes the pre-2014 estimation model be used even at a high compatibility level; if you see it on an old system, you know someone "solved" a regression this way. DBCC TRACESTATUS(-1) came back empty: there are no global trace flags on this server. On old servers flags such as 4199 (optimizer fixes) or 9481 (the old estimation model) are common; today database scoped configurations have replaced most of them.

From 2016 to 2025: The Engine, Version by Version

Lining up by version the features we measured one by one in the earlier posts of the series gives this table:

One distinction matters when reading the table. Most features in the left column depend on the compatibility level: upgrading the engine isn't enough, you also have to raise the database's level; the opening experiment shows that. Most in the right column are engine features and work independently of the compatibility level: you don't need to change the level to turn on ADR, optimized locking or TDS 8.0. There are exceptions: new T-SQL functions such as REGEXP_LIKE and GENERATE_SERIES require a certain level; the Msg 195 in the opening is the result.

Lifecycle: Which Version Until When?

Under Microsoft's fixed lifecycle policy every version gets five years of mainstream support (including non-security fixes) and five years of extended support (security updates only). As of today:

• SQL Server 2016: extended support ended on July 14, 2026. From now on security updates come only through paid ESU (Extended Security Updates), for at most three years.
• SQL Server 2017: extended support ends on October 12, 2027. Less than a year left.
• SQL Server 2019: mainstream support ended on February 28, 2025; extended support runs until January 8, 2030.
• SQL Server 2022: mainstream support until January 11, 2028, extended support until January 11, 2033.
• SQL Server 2025: released on November 18, 2025; mainstream support until January 6, 2031, extended support until January 6, 2036.

If you have a system running on 2016 or 2017, upgrading is no longer a choice but a matter of the calendar. The good news: moving to the target version's engine doesn't require changing the compatibility level right away; the risk can be split into two separate steps.

An Upgrade Playbook

The flow Microsoft recommends with Query Store separates the engine change from the change in query behavior:

1. Upgrade the engine and leave the compatibility level at its old value. The database is on 2025 but runs at level 130 or 140; plans stay largely the same. 2. Turn on Query Store and collect the workload for a while; long enough to cover rare jobs such as month-end. 3. Raise the compatibility level. 4. Find the regressed queries in Query Store and force the old plan if needed. 5. Apply the permanent fix (index, query, statistics) and remove the forcing.

We ran a shortened version of the flow in the lab. We run the reporting query three times at 140, then three times at 170, and look at Query Store:

ALTER DATABASE sqls_19 SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
ALTER DATABASE sqls_19 SET COMPATIBILITY_LEVEL = 140;
/* rapor 3 kez */
ALTER DATABASE sqls_19 SET COMPATIBILITY_LEVEL = 170;
/* rapor 3 kez */
EXEC sys.sp_query_store_flush_db;

SELECT q.query_id, p.plan_id, p.compatibility_level, p.is_forced_plan,
       CASE WHEN CAST(p.query_plan AS nvarchar(max)) LIKE N'%EstimatedExecutionMode="Batch"%'
            THEN 'Batch' ELSE 'Row' END AS execution_mode,
       SUM(rs.count_executions) AS executions
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE t.query_sql_text LIKE N'SELECT BookId, SUM(Quantity)%'
GROUP BY q.query_id, p.plan_id, p.compatibility_level, p.is_forced_plan, CAST(p.query_plan AS nvarchar(max))
ORDER BY p.plan_id;

query_id|plan_id|compatibility_level|is_forced_plan|execution_mode|executions
--------|-------|-------------------|--------------|--------------|----------
1|1|140|0|Row|3
1|2|170|0|Batch|3

The same query, two plans: row mode compiled at 140, batch mode compiled at 170. Query Store keeps, separately for each plan, the compatibility level it was compiled at and its runtime statistics; the Regressed Queries report does exactly this comparison. Here both plans scan OrderItems with 9,964 logical reads; the difference is on the CPU (Central Processing Unit) side. Suppose the new plan got worse for some query. Forcing the old plan is one line:

EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 1;
/* rapor 3 kez, compatibility level 170'teyken */

query_id|plan_id|compatibility_level|is_forced_plan|execution_mode|executions
--------|-------|-------------------|--------------|--------------|----------
1|1|170|1|Row|6
1|2|170|0|Batch|3

plan_id|force_failure_count|last_force_failure_reason_desc
-------|-------------------|------------------------------
1|0|NONE
2|0|NONE

The database is at 170, but the query ran with the shape of the forced plan, in row mode: plan 1's execution count went from 3 to 6, and it now shows as compiled under 170. force_failure_count is 0; forcing succeeded. One thing not to forget about plan forcing: if the forced plan depends on an index and that index is dropped, forcing fails and last_force_failure_reason_desc tells you why. Forcing is temporary insurance, not a permanent fix. We covered the details of Query Store and automatic tuning in the 8th post of the series (Query Store and Intelligent Query Processing).

If You're Upgrading to SQL Server 2019/2022

If your target is 2019 or 2022 rather than 2025, the same plan applies, but your gain is limited to the rows in the table. Moving to 2019 and raising the level to 150 gives you four of the five measurements in the opening (table variable, MSTVF, batch mode, UDF inlining); optimized locking, REGEXP_* and OPPO (Optional Parameter Plan Optimization) aren't there. 2022 and 160 add Parameter Sensitive Plan optimization and the feedback family; Query Store comes on by default for new databases, but it can stay off in migrated old databases, so check. Because 2019's mainstream support has ended, 2022 or 2025 makes more sense as the target of a new upgrade; 2022's extended support runs until 2033, 2025's until 2036.

Watch Out in Production

Don't change the engine and the compatibility level on the same day. If you change both at once, you can't tell whether a regression comes from the engine, the new estimation model, or a new IQP (Intelligent Query Processing) feature. Leave enough time between the two steps for Query Store to collect a baseline.

Test the application's own version checks. Entity Framework Core (EF Core) generates SQL in its SQL Server provider according to the targeted version; for example EF Core 8 and later use OPENJSON when translating Contains, which requires compatibility level 130. In a database running at a very old level these queries fail; you can tell EF Core the level with UseCompatibilityLevel(...). We saw these changes in the version posts of the EF Core series.

Scan for deprecated features. Before upgrading, the "Deprecated Features" counters in sys.dm_os_performance_counters, or the deprecation_announcement and deprecation_final_support Extended Events, show whether features removed or to be removed in the target version are in use.

Don't forget the edition. In 2025 Standard edition went up to 32 cores and 256 GB of memory, and the database limit of Express edition became 50 GB (post 1 of the series); when moving from an old server, review the licensing plan against these limits too.

Common Pitfalls

Upgrading the engine and forgetting the compatibility level. The opening table is this pitfall itself: you paid for a 2025 license, but the database runs with 2016 behavior. Most new features never kick in.

"Solving" a regression with a trace flag. 9481 or LEGACY_CARDINALITY_ESTIMATION sends the whole database back to the old model for a few problem queries. Try per-query tools first: plan forcing in Query Store, Query Store hints, or a query-level USE HINT.

Turning Query Store on after the upgrade. If you don't collect a baseline before the upgrade, you have nothing to compare against when asking "is the new plan worse?".

Not checking the level of a database moved by restore. When a backup is restored to a newer version, the compatibility level stays as it is if the target version supports it; if not, it's raised to the lowest supported level. Either way, verify the result in sys.databases.

A Small Experiment

★☆☆ Examine one of your own databases (or the series' lab database) with sys.databases and sys.database_scoped_configurations. What's the compatibility level? Is any setting different from the default?

★★☆ Repeat the five opening measurements at compatibility level 170 with ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF. Which column changes and which doesn't?

★★★ Run the parameter sniffing demo from post 7 of the series at 150, 160 and 170 and compare how many plans appear in Query Store at each level. How do Parameter Sensitive Plan optimization's plans show up in sys.query_store_query_variant?

We end the SQL Server Deep Dive series here. We went through the inside of the engine: how it keeps data in pages, indexes, the optimizer's estimates, locks, code inside the database, and solving a complaint by measuring. The natural next step is to look beyond a single server: in the upcoming Database Performance and Scaling series we'll cover partitioning, read replicas, High Availability/Disaster Recovery (HA/DR) and sharding.