In short
Often yes, for some of your queries. A newer engine can run the same queries with less CPU, without a single line of code changing. That is not guaranteed, though: other queries get a different plan after the upgrade, and that plan can be slower.
Whether your application benefits depends on a comparison of your own queries, not on the version number. That comparison works if you change the three things an upgrade affects one after another: first the engine, then the compatibility level, then individual new features.
Some of the most effective optimizations also depend on Enterprise Edition rather than on the version.
If things have already gotten slower after the upgrade, the cause is usually changed plans. Query Store shows them, provided it was already recording beforehand.
An upgrade changes three things, not one
When people say “upgrade,” they usually mean the new version. For performance, three separate levers count, and they take effect at different times:
- The engine. New version, new build. It takes effect immediately, as soon as the database runs on the new server: memory management, execution, many optimizations.
- The compatibility level (CL) of the database. It determines the rules by which the optimizer builds plans. A restored database keeps its previous level as long as that is not below the minimum of the new version; on SQL Server 2025 the minimum is 100. The level only changes when someone raises it.
- Individual new features. Many can be switched on and off per database. Quite a few only work from a certain compatibility level, and some only in the Enterprise Edition.
If you switch all three at once, you only see the sum afterward. Whether a query got faster because of the engine, slower because of the new level, or just different because of a feature can then no longer be separated. That is why we go step by step, and why the picture for this article is a staircase: three steps up from where you are today.
- Step 0todaychanges: nothing
- Step 1new enginechanges: version and build
- Step 2new compatibility levelchanges: optimizer rules
- Step 3featureschanges: one feature at a time
| Step | What changes | What stays the same |
|---|---|---|
| 0 · today | nothing | old version, old compatibility level |
| 1 · new engine | version and build | compatibility level, database settings |
| 2 · new compatibility level | optimizer rules | engine from Step 1 |
| 3 · features | one feature at a time | everything else |
What actually happens on restore. In the lab we backed up a database on SQL Server 2022 and restored it on SQL Server 2025. We deliberately set two settings differently from the defaults. Result: compatibility level 160, a database-level MAXDOP of 2 and the disabled Parameter Sensitive Plan Optimization (PSP) were kept, and data and checksums were identical. The new switches for SQL Server 2025 features, on the other hand, were ON right away, including OPTIONAL_PARAMETER_OPTIMIZATION. The feature still had no effect, because it requires compatibility level 170.
It follows that a newly created test database is not an upgrade test. It gets the defaults of the new version, while your production database brings its old ones along.
Applies to Steps 1 and 2
Experiment 1: same code, new engine, then new compatibility level
Expectation If the engine alone brings something, it must show up with unchanged code and an unchanged compatibility level.
Change The same stored procedures, the same data (500,000 rows with unevenly distributed and correlated values), the same statistics. Once on SQL Server 2022, once on SQL Server 2025, both as Enterprise Developer edition with compatibility level 160. Then the same with compatibility level 170.
Observation Throughput in executions per second, CPU in milliseconds per reporting query execution.
| Query type | 2022, CL 160 | 2025, CL 160 | 2025, CL 170 |
|---|---|---|---|
| Reporting query with join and aggregation, 1 session | 32.07 | 38.60 | 38.22 |
| CPU per reporting query | 107–109 ms | 87–89 ms | – |
| Mixed workload, 8 sessions (longer control run) | 492.00 | 618.83 | – |
| Short point lookup, 1 session | 1,478.85 | 1,514.01 | 1,494.67 |
On SQL Server 2025, the reporting query needs almost one fifth less CPU. The mixed workload completes 25.8 percent more executions per second. Both results come with the compatibility level unchanged at 160. Moving to level 170 added nothing for this reporting query. With the short point lookups, our load generator was the bottleneck; that tells us neither a gain nor a loss.
Conclusion The engine can bring a clear advantage even at Step 1, without touching the compatibility level. That is one reason to separate the steps: anyone who jumps straight to compatibility level 170 credits the gain to the new level and, if something gets worse, looks in the wrong place.
The 25.8 percent apply to this workload. They are not a rate you can transfer to your application. Your queries, data and servers decide.
Applies to Step 3
Experiment 2: a feature is set to ON
Each version brings features that are meant to improve plans. For SQL Server 2025, examples are Optional Parameter Plan Optimization (OPPO) and CE feedback for expressions. But a switch set to ON does not tell you anything yet. So we check four things in order:
- 01
Available
Does the feature exist in your edition and at your compatibility level?
- 02
Suitable
Does your query match the pattern the feature was built for?
- 03
Used
Does the actual plan show that it took effect?
- 04
Useful
Has the query become measurably faster or more stable as a result?
Expectation For a search form with an optional filter (WHERE @Customer IS NULL OR Customer = @Customer), OPPO should build separate plans for “filter empty” and “filter set.”
Change The same procedure on SQL Server 2025 at compatibility level 170, with OPPO on and off.
Observation With OPPO, the plan contained a dispatcher with two variants: one for NULL, one for all other values. With two optional filters there were four variants. So the feature takes effect. Within the “filter set” variant, however, rare and frequent values still share one plan. In our mixed run, rare values at times took about one millisecond, while frequent values with the same plan took about 500 milliseconds.
Conclusion OPPO solves the “empty or set” problem. It does not solve unevenly distributed values within the filter that is set. That is what PSP is meant for. In our lab tests, PSP did not create a variant in any of the forms we tried; the telemetry reported that the skew in the data was not sufficient. That is not evidence against the feature, but it is evidence that ON alone proves nothing.
It is similar with CE feedback for expressions. For two dependent filters, the optimizer without the feature estimated 500 rows, and 5,000 actually came back. With the feature, the estimate was right, also for other queries with the same filter expression. With four dependent columns, however, the estimate stayed wrong, although the plan carried a feedback marker.
Which of these features are available to you at all also depends on the edition. That is what the next section is about.
New version or Enterprise Edition?
Microsoft promoted SQL Server 2016 with the phrase “It Just Runs Faster.” It meant the new version: engine improvements that, according to Microsoft, take effect in every edition. That is Step 1 of our staircase.
A second source of speed has little to do with the version: the edition. Microsoft does not give a figure for how much faster Enterprise is. The edition overview does show where the differences are:
| Area | Standard Edition | Enterprise Edition |
|---|---|---|
| Processor cores | 2022: up to 24, 2025: up to 32 | as many as the operating system provides |
| Memory for the buffer pool | 2022: 128 GB, 2025: 256 GB | no edition limit |
| Batch mode | only for columnstore, with at most two parallel threads | also for regular tables, without that limit |
| Optimizations that learn | PSP, OPPO, Optimized Plan Forcing | plus adaptive joins, memory grant feedback, CE and DOP feedback, automatic plan correction |
| Reading large tables | usual read-ahead | shared scans and more read-ahead |
What this means for Experiment 1. Both servers in Experiment 1 ran as Enterprise Developer edition. The reporting query used batch mode on rowstore on SQL Server 2022 as well as on 2025, which is an Enterprise feature. On Standard Developer edition, the same query ran in row mode on identical hardware. So the 25.8 percent is a comparison within Enterprise. We have not reliably measured how large the gap between Standard and Enterprise is for this query.
Conclusion. With a new version, Standard gets the engine improvements, and with SQL Server 2025 also higher limits for cores and memory. It does not get the Enterprise features. Changing the edition is therefore a change of its own next to the staircase: it determines which features are available to choose from at Step 3, and it should be measured separately in the same way. You can do that without buying a license: SQL Server 2025 offers a Developer edition for both Enterprise and Standard.
Applies to Step 3
Experiment 3: fewer locks, different result
Not every change affects speed. Some change behavior.
Expectation Optimized locking in SQL Server 2025 should hold fewer locks and reduce blocking.
Change 1,000 modified rows in an open transaction, with optimized locking off and on. Then a precisely timed sequence of two competing updates, once without and once with optimized locking and read committed snapshot isolation (RCSI).
Observation Instead of a median of 1,060 held locks, there were three. In the competing sequence, the row ended with the value 3 in all five repetitions without the new locking logic, and with the value 2 in all five repetitions with optimized locking and RCSI. Microsoft describes exactly this scenario in its documentation.
Conclusion Fewer locks does not mean the same result, only faster. If your application relies on the order in which competing changes take effect, that should be put to the test before you switch the feature on; for such cases, Microsoft recommends a stricter isolation level. This is not an engine bug, but it is a change that can affect your business logic. Optimized locking is off by default in SQL Server 2025, is enabled per database, and requires accelerated database recovery (ADR).
Why before-and-after comparisons often mislead
We ran into three traps in the lab ourselves.
The first call decides the plan. A procedure is compiled for the values it is called with first, and it keeps that plan. In a search procedure that splits the optional filter by hand into two branches with IF/ELSE, a frequent value took 690 to 743 milliseconds when the first call used NULL, and 17 to 20 milliseconds when a frequent value came first. If the test on the old version uses a different first call than the test on the new one, you are comparing two plans, not two versions. More on this in the article One query, many plans.
Learned state is lost. Features such as CE feedback learn from previous executions. After a restart, we found that of 17 learned entries, only four were still there immediately; the rest built up again only with further executions. Microsoft describes persisting this state as periodic and potentially lossy. So do not measure only the first day after the upgrade, and separate warm-up from steady state.
The upgrade rarely comes alone. Often the server, virtual machine, core count, memory or storage system changes along with the version. Any of these changes can shift plans and run times. Write down everything that is different before you attribute it to the version.
Slower after the upgrade? How to narrow it down
- Establish what has changed. Version and build, compatibility level, settings per database, edition, hardware. The two queries below only read the most important database values; we have tested them on SQL Server 2017 to 2025.
- Find the slow queries. Query Store shows under “Regressed Queries” which queries take more time since a given point in time and whether their plan has changed. For that, it must have been recording before the upgrade. New databases from SQL Server 2022 onward have it on by default; databases that arrive through restore or upgrade keep their previous setting.
- Compare plans. Show the old and the new plan side by side to see where the estimate and the access path differ. We present a tool for this in the article sp_BlitzPlanCompare.
- Roll back selectively, not across the board. For individual queries, you can force the earlier plan through Query Store; in the Enterprise Edition, automatic plan correction can do this for you. From SQL Server 2022 on, Query Store hints can give a query, in any edition, the legacy cardinality estimator or an older compatibility level, for example, without changing the code. Resetting the compatibility level of the whole database works without a reinstall and is a valid escape hatch. But it also takes the benefits of the new rules away from all other queries.
- Clarify the cause. A forced plan is a stopgap. To find out why the new plan is worse, follow the path in our performance guide: SQL Server Is Sometimes Fast and Sometimes Slow.
Read-only – changes nothing in your database
-- Read-only: compatibility level and Query Store per database
SELECT name,
compatibility_level,
is_query_store_on,
is_read_committed_snapshot_on
FROM sys.databases
ORDER BY name;
-- Read-only: selected settings of the current database
-- (rows are missing if the version does not know the setting)
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN (N'MAXDOP', N'LEGACY_CARDINALITY_ESTIMATION',
N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION',
N'OPTIONAL_PARAMETER_OPTIMIZATION',
N'CE_FEEDBACK', N'DOP_FEEDBACK',
N'CE_FEEDBACK_FOR_EXPRESSIONS',
N'OPTIMIZED_SP_EXECUTESQL');How we do it For exactly this before-and-after comparison, our engineers use PSG QX. It analyzes the Query Store that your database wrote before and after the upgrade and sorts the queries: newly added, with a worse plan, called more often, or more expensive per call. QX classifies each comparison as comparable, limited or not possible, with a reason, instead of claiming an improvement or a regression. Here too, the prerequisite is a Query Store that was already recording before the upgrade. More about PSG QX
Before the upgrade: the approach in seven steps
- 01
Turn on Query Store on the old version and collect at least a few weeks of normal operation, including month-end close or other peaks. That is your baseline.
- 02
Name the most important query families: the ten to twenty queries that consume the most time or CPU or are critical for the business, each with typical and extreme parameters.
- 03
Restore a real copy, not a newly created database. Check which settings were carried over.
- 04
Step 1: new version, old compatibility level. Compare the same queries with a defined first call.
- 05
Step 2: raise the compatibility level and compare again. Investigate queries that get slower one by one.
- 06
Step 3: evaluate new features one at a time, using the four test questions. Check features that change behavior, such as optimized locking, against the business logic.
- 07
Observe after the switch and decide beforehand when you will go back: to a forced plan, a hint or the old compatibility level.
Microsoft describes the same basic procedure: upgrade without changing the compatibility level, Query Store over a business cycle, then raise the compatibility level and catch regressions through Query Store.
Frequently asked questions
Is a new version guaranteed to be faster?
No. Microsoft improves the engine with every version but does not promise that every query benefits. A typical reason for regressions is the new optimizer rules that come with the higher compatibility level: the estimates change, and with them the plans. That is why Microsoft’s own procedure includes a way back for individual queries through Query Store. The only thing that is certain is what you have measured with your own queries.
Will SQL Server 2025 be faster than our SQL Server 2019?
For some queries very likely, for others not. There is no general percentage. The answer only becomes reliable with your own queries at Step 1 and 2.
Do we have to raise the compatibility level right away?
No. The database keeps running on the new version at the old level, and the engine brings its benefits anyway. Many new features do require the new level, though. Raise it once you have checked Step 2.
Should we just switch on all new features and hope it helps?
No, and that is usually not the point anyway. Most features are on as soon as the matching compatibility level is set. The real question is what you do when one of them hurts a query. That happens: for scalar UDF inlining, Microsoft has fixed bugs and added exclusions over many cumulative updates (CU), and for PSP it has documented and fixed bugs in its interaction with Query Store. So apply current cumulative updates before you evaluate features. Experienced practitioners in the community consistently advise the same path as Microsoft: first Query Store and a baseline, then raise the level, and if a query suffers, intervene for that query specifically. For that, each feature has a per-database setting and usually a hint that you can set through Query Store without changing code. Turning a feature off for the whole database is rarely necessary.
Is Enterprise Edition worth it for performance?
For some workloads clearly, for others hardly. According to Microsoft, batch mode on rowstore, the feedback features, adaptive joins and automatic plan correction exist only there; in addition, the limits for cores and memory fall away. A test with the two Developer editions shows whether your queries benefit. For edition and core count, the rule is: measure first, then license. We don’t sell licenses.
Our vendor mandates a specific compatibility level. Is the upgrade still worthwhile?
It can be. The engine works at a mandated level, too, as Experiment 1 shows. Features that need a higher level stay out of reach.
How long does a reliable comparison take?
That depends on the number of query families. Most of the work is preparation: baseline, copy, defined parameters. The measurement itself is then manageable.
Method and limits
All measurements come from our lab series of Oct 12, 2026: SQL Server 2022 CU27 and SQL Server 2025 CU9, for all experiments as Enterprise Developer edition, and for the edition comparison additionally SQL Server 2025 as Standard Developer edition, in containers with four CPUs and 4 GiB of maximum SQL Server memory each, synthetic data with 500,000 rows, identical statistics. Each performance variant ran in at least five measurement windows after a warm-up phase. Variants whose windows differed from each other by more than ten percent were not counted or were repeated with longer windows. Recording the individual times lengthened the runs in our calibrations by at most 3.59 percent. We ran the two read-only queries on Oct 23, 2026 on SQL Server 2017, 2019, 2022 and 2025.
This is a lab, not a customer environment. The values show which effects can occur and how to separate them. They are not a forecast for your application. Parameter Sensitive Plan Optimization, adaptive joins and DOP feedback did not take effect in our tests; positive examples for them are still outstanding.
Microsoft sources (retrieved Oct 23, 2026): Compatibility level · Query Store usage scenarios: upgrade · Intelligent query processing · Editions of SQL Server 2025 · Optimized locking · Query Store hints · What’s new in SQL Server 2025: edition changes · Reading pages: read-ahead and advanced scanning · “SQL 2016 – It Just Runs Faster” (archive) · Change the compatibility level and use Query Store · Scalar UDF inlining · Parameter Sensitive Plan Optimization · What’s new in SQL Server 2022: Query Store
We help with the decision
In the Upgrade & license check we compare your most important queries across the three steps on your data and name winners, losers and open risks. We also assess edition and core count from your point of view. If the upgrade is already running, we use PSG QX to analyze the Query Store from before and after.