Performance Degradation after moving from SQL 2019 to SQL 2025

Question

Performance Degradation after moving from SQL 2019 to SQL 2025

Mantini, Ronald M 6

I am in the processing of upgrading our SQL environment from 2019 to 2025. We are troubleshooting perf issues in our pre prod env right now before upgrading production and I have a fairly simple query that loads 1.6m rows to a temp table and typically takes around 2-3 minutes to complete. This code has not changed for many years, its part of a larger aggregation and is called hundreds of times during the full run.

When running under SQL 2019 compatibility the query finishes in about 2.5-3mins.

After upgrading to 2025 and updating the db compatibility to 170 (2025) this query runs for over 3 hours, every time. At first I assumed it was due to the upgrade and creating a new plan or similar but I can reproduce this instantly every time simply by changing the Compat level back to 170.

I tested at version 160 (2022) and actually get the best perf, as low as 1 minute.

The from and Joins are below. I have been looking at the execution plans and between 2019 and 2025 they don't look much different, all the slowness is in a Hash Match(right Anti Semi Join). The fastest option (2022 compat) uses a Nested Loops (Left Outer Join) instead.

I'm baffled, so far this is the only piece of code amongst hundreds of queries we have noticed having this problem. There may be more but this one has me stumped so I haven't moved on to the rest yet. Any feedback from someone as to why I am seeing this would be appreciated. I just did the upgrade 2 days ago and started the query store, but I have been flipping compatibility so often I probably need to just let it run for a while, but I can't let it run when its taking 3 hours during this aggregation :).


INTO #TempTable2
FROM DB1.dbo.TABLE1 FRE (NOLOCK)
JOIN DB2.dbo.TABLE2 SI
	ON FRE.fac= SI.fac
JOIN #TempTable1 FSV
	ON FSV.fac= FRE.fac
	AND FSV.trid= FRE.trid
LEFT JOIN DB3.dbo.Table3 AEX  NOLOCK
	ON SI.[Site] = AEX.[Site]
	AND (FRE.obj1 = AEX.obj OR FRE.obj2 = AEX.obj)
	AND FRE.start_time BETWEEN AEX.StartTime AND AEX.EndTime
WHERE AEX.obj IS NULL

Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-07T20:50:23.0733333+00:00
Permit me to first comment on the NOLOCK hints. This is highly dangerous to use in production environments. When you use NOLOCK, any of these can happen:

You read uncommitted and possibly inconsistent data.

You fail to read data that has been committed for a long time.

You may read the same data twice.

The query will fail with 601.

So my strong advice is to remove the NOLOCK hints. If you think blocking is a problem, you should consider using any of the snapshot modes.

Now over to your actual question. It is not uncommon to see performance regression after SQL Server upgrades. With every version of SQL Server, there are new enhancements to the optimizer. Unfortunately, while they (hopefully) are improvements on a statistical level, they can backfire in the individual case.

If I understand this correctly, you get acceptable performance when you are on compat level 150, but when you switch to level 170, things go bad. This means that there is a simple short-term solution: Activate Query Store and run the query with compat level 150. Now you can force this plan through Query Store and switch to compat level 170.

However, for a long-term solution you need to dig deeper and see what you can do to enhance the query plans. As a start, I suggest that you share the query plans for the slow and the fast executions. We want the XML file, not the graphics. When you upload the files here, you will need to change the file type from .sqlplan to .xml for the forum software to accept it.

Once we have the plans, we may ask for more information about the tables.
Mantini, Ronald M 6 Reputation points

2026-05-07T21:36:58.6033333+00:00

Thanks for the quick followup @Erland Sommarskog . #1, very aware of what nolock does :), this is legacy code thats been running a large aggregation for 8+ years, its works fine in this case. For various reasons RCSI isn't an option.

Asking for security clearance to post the exec plans, will do if I get the green light. Thanks
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-07T21:41:01.84+00:00

For various reasons RCSI isn't an option.

In that case you can enable ALLOW_SNAPSHOT_ISOLATION and run with true snapshot isolation. If the query has been running for eight years without producing errors, I suggest NOLOCK is not needed, because in that case there is no concurrent activity that conflict and cause errors. Or no one has simply noticed the incorrect results...
Mantini, Ronald M 6 Reputation points

2026-05-11T15:53:50.89+00:00
No go on posting our execution plans. I'm looking at the query in the query store, I've got the query hash, but the query IDs are different for the one under compat 170 vs 160. How do I force a plan onto another query if the query store views them as 2 separate queries? When I run the below thats the error Im getting. Thanks again for your help.

sp_query_store_force_plan

Query plan with provided plan_id (24) is not found in the Query Store for query (303). Check the plan_id value and rerun the command.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-11T21:20:40.5933333+00:00

That you are getting two different query ids is unexpected, and it is nothing I repeat when I make a quick test. However, there is one specific situation when this could happen. If you look at the query text in the execution plan for this query when you are in compat mode 170, you see any option (PLAN PER VALUE) being added?

And, yes, when you get different query ids, its difficult to force the plan.

And you are running both in the same database, flipping the compat level forth and back, right?

Is this a query in a stored procedure, or is it something submitted directly from the client?

Too bad that you did not get OK to share the plans. Well, there is one more possible option: Use SQL Sentry Plan Explorer which includes an anonymisation tool which replaces table names with Table001 etc. I much prefer to work with non-anonymised plans, but an anonymised plan is better than nothing at all. If your management cannot accept this, your only option would be a support case. It will cost your company more, but it saves you from posting things in public.
Mantini, Ronald M 6 Reputation points

2026-05-11T23:40:05.7833333+00:00

It runs as a stored proc but once I identified the problem query I have been running just that portion manually. I at first had 2 separate sessions going, I would run one, find the query/hash, let it complete, flip the compat level, re-connect in the other session (wasn't sure if that mattered or not), run again, get the hash etc. Code was identical in both sessions/windows. Then I ran in the same window before/after flipping compat when I realized it was being viewed as 2 different queries, but same result.

I will get back to this tomorrow had other problems today, and I again greatly thank you for your help. I will look at the anonymisation tool too.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-12T07:35:33.86+00:00

You need to run the stored procedure. If you run the query separately, you will get a different query id than if you run it outside the procedure, as the object id is an attribute of the query. I suspect the reason that you got different query ids when you ran it manually is that you accidentally had a trailing space or something similar in the other window.

Erland Sommarskog 134K MVP Volunteer Moderator

By the way, you can use this query to find the query id:

SELECT *
FROM   sys.query_store_query q
JOIN   sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE  q.object_id = object_id('YourSP')
  AND	 qt.query_sql_text LIKE '%text from your query%'

1 answer

Your answer

Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-07T20:50:23.0733333+00:00

Permit me to first comment on the NOLOCK hints. This is highly dangerous to use in production environments. When you use NOLOCK, any of these can happen:

You read uncommitted and possibly inconsistent data.

You fail to read data that has been committed for a long time.

You may read the same data twice.

The query will fail with 601.

So my strong advice is to remove the NOLOCK hints. If you think blocking is a problem, you should consider using any of the snapshot modes.

Now over to your actual question. It is not uncommon to see performance regression after SQL Server upgrades. With every version of SQL Server, there are new enhancements to the optimizer. Unfortunately, while they (hopefully) are improvements on a statistical level, they can backfire in the individual case.

If I understand this correctly, you get acceptable performance when you are on compat level 150, but when you switch to level 170, things go bad. This means that there is a simple short-term solution: Activate Query Store and run the query with compat level 150. Now you can force this plan through Query Store and switch to compat level 170.

However, for a long-term solution you need to dig deeper and see what you can do to enhance the query plans. As a start, I suggest that you share the query plans for the slow and the fast executions. We want the XML file, not the graphics. When you upload the files here, you will need to change the file type from .sqlplan to .xml for the forum software to accept it.

Once we have the plans, we may ask for more information about the tables.
Mantini, Ronald M 6 Reputation points

2026-05-07T21:36:58.6033333+00:00

Thanks for the quick followup @Erland Sommarskog . #1, very aware of what nolock does :), this is legacy code thats been running a large aggregation for 8+ years, its works fine in this case. For various reasons RCSI isn't an option.

Asking for security clearance to post the exec plans, will do if I get the green light. Thanks
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-07T21:41:01.84+00:00

For various reasons RCSI isn't an option.

In that case you can enable ALLOW_SNAPSHOT_ISOLATION and run with true snapshot isolation. If the query has been running for eight years without producing errors, I suggest NOLOCK is not needed, because in that case there is no concurrent activity that conflict and cause errors. Or no one has simply noticed the incorrect results...
Mantini, Ronald M 6 Reputation points

2026-05-11T15:53:50.89+00:00

No go on posting our execution plans. I'm looking at the query in the query store, I've got the query hash, but the query IDs are different for the one under compat 170 vs 160. How do I force a plan onto another query if the query store views them as 2 separate queries? When I run the below thats the error Im getting. Thanks again for your help.

sp_query_store_force_plan

Query plan with provided plan_id (24) is not found in the Query Store for query (303). Check the plan_id value and rerun the command.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-11T21:20:40.5933333+00:00

That you are getting two different query ids is unexpected, and it is nothing I repeat when I make a quick test. However, there is one specific situation when this could happen. If you look at the query text in the execution plan for this query when you are in compat mode 170, you see any option (PLAN PER VALUE) being added?

And, yes, when you get different query ids, its difficult to force the plan.

And you are running both in the same database, flipping the compat level forth and back, right?

Is this a query in a stored procedure, or is it something submitted directly from the client?

Too bad that you did not get OK to share the plans. Well, there is one more possible option: Use SQL Sentry Plan Explorer which includes an anonymisation tool which replaces table names with Table001 etc. I much prefer to work with non-anonymised plans, but an anonymised plan is better than nothing at all. If your management cannot accept this, your only option would be a support case. It will cost your company more, but it saves you from posting things in public.
Mantini, Ronald M 6 Reputation points

2026-05-11T23:40:05.7833333+00:00

It runs as a stored proc but once I identified the problem query I have been running just that portion manually. I at first had 2 separate sessions going, I would run one, find the query/hash, let it complete, flip the compat level, re-connect in the other session (wasn't sure if that mattered or not), run again, get the hash etc. Code was identical in both sessions/windows. Then I ran in the same window before/after flipping compat when I realized it was being viewed as 2 different queries, but same result.

I will get back to this tomorrow had other problems today, and I again greatly thank you for your help. I will look at the anonymisation tool too.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-12T07:35:33.86+00:00

You need to run the stored procedure. If you run the query separately, you will get a different query id than if you run it outside the procedure, as the object id is an attribute of the query. I suspect the reason that you got different query ids when you ran it manually is that you accidentally had a trailing space or something similar in the other window.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2026-05-12T21:18:37.7166667+00:00

By the way, you can use this query to find the query id:

SELECT * FROM sys.query_store_query q JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE q.object_id = object_id('YourSP') AND qt.query_sql_text LIKE '%text from your query%'

Answer 1

The behavior matches a query-plan regression caused by optimizer and Cardinality Estimator (CE) changes at higher compatibility levels. The query itself is unchanged, but the CE and optimizer behavior differ between compat 150 (SQL 2019), 160 (SQL 2022), and 170 (SQL 2025), which can lead to different join strategies and dramatic performance differences.

Key points from the scenario and context:

Compatibility level drives CE and optimizer behavior
- From SQL Server 2014 onward, new CE and optimizer changes are tied to database compatibility level, not just engine version.
- Different compat levels can choose different join types and plans for the same query and data. In your case:
  - Compat 150 (2019) → ~2.5–3 minutes
  - Compat 160 (2022) → ~1 minute (Nested Loops join)
  - Compat 170 (2025) → >3 hours (Hash Match Right Anti Semi Join)
- This is consistent with known patterns where newer CE assumptions and optimizer changes improve many workloads but regress some specific queries.
Plan choice differences are a known cause of regressions after upgrade
- Documentation highlights that upgrading compatibility level can introduce CE changes and different plan choices that may degrade performance for some queries, even if most workloads improve.
- The 2016/2017 example in the context (compat 120 vs 130) shows exactly this pattern: a plan flips from parallel to serial and performance collapses for a large result set. The recommended mitigations there apply directly to this scenario as well: revert compat level or force the better plan.
Recommended mitigation pattern Based on the documented guidance for similar regressions:
1. Run the database at a lower compatibility level as a baseline
- Since compat 160 gives the best performance for this query, one pragmatic option is to keep the database at 160 on SQL Server 2025 while the regression is investigated.
- This is explicitly recommended in similar upgrade regressions (e.g., reverting from 160 to 150 when moving to SQL 2022/2025) to confirm that the issue is optimizer/CE-related rather than hardware or configuration.
1. Use Query Store to identify and fix the regression
- Enable Query Store (if not already) and let it capture plans at both compat levels.
- Identify this query in Query Store and compare:
  - Runtime statistics
  - Plans at compat 160 vs 170
- If the compat 160 plan is consistently better, use Query Store to force that plan while running at compat 170. This is the recommended pattern in the documentation for CE/compat-level regressions.
1. Alternatively, force the good plan with a hint
- If Query Store cannot be used (or as a temporary workaround), capture the good plan (from compat 160 or 150) and apply it via USE PLAN query hint in the specific statement. The context explicitly calls out USE PLAN as a way to keep the older, better plan while running at the newer compatibility level.
Broader troubleshooting steps If more queries show regressions as compat 170 is used:
- Confirm environment parity:
  - Same data, statistics, and indexes between pre-prod and prod.
  - Same hardware characteristics (CPU count, memory, power plan) and SQL Server build level.
- Compare plans and statistics:
  - Ensure statistics are up to date with similar sampling on all relevant tables.
  - Check whether the new CE assumptions at higher compat levels are leading to misestimates (e.g., join cardinality, filter selectivity) that push the optimizer toward an expensive Hash Match Anti Semi Join.
- If regressions are limited to a small set of queries, use Query Store plan forcing per-query rather than lowering compat level for the entire database.
Strategy for production rollout
- Before upgrading production, run representative workloads in pre-prod with:
  - Compat 150 and 160 to establish baselines.
  - Compat 170 with Query Store enabled and capture regressions.
- For each regressed query, either:
  - Fix the query/indexing if a clear pattern emerges, or
  - Force the known-good plan via Query Store.
- If regressions are widespread and cannot be mitigated quickly, consider running production at compat 160 on SQL Server 2025 initially, then gradually move to 170 as specific regressions are addressed.

In summary, the behavior is consistent with a CE/optimizer change at compat 170 that leads to a poor join strategy for this query. The documented and supported mitigations are: temporarily use a lower compatibility level (160), and/or use Query Store or USE PLAN to force the better plan while remaining on SQL Server 2025.

References:

Share via

Performance Degradation after moving from SQL 2019 to SQL 2025

1 answer

Your answer