how to resolve degraded sql performance since upgrading from sql 2016 to sql 2022

Question

how to resolve degraded sql performance since upgrading from sql 2016 to sql 2022

Greg Booth 1,496

We have a sql instance ( on premise) that was running sql 2016. Query store was not enabled. We have a large workload that runs overnight, hundreds of stored procedures.

We have upgraded to sql 2022, but had an approximately 10% increase of the duration of the overnight workload and would like to resolve this.

So far we have

a) changed the database to sql 2022 compatibility mode

b) enabled query store (operation mode = read write, query capture mode = auto)

c) changed MAXDOP to 8 ( the servers has 8 vcpu), ( maxdop was set to 0 before) and the cost of parallelism to 50 ( it was 5 before)

None of these changes have resolved the performance degradation.

Is there anything else i can try ?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Erland Sommarskog 133K MVP Volunteer Moderator

What edition of SQL Server do you have?

Only enabling Query Store is not that likely to improve matters(*), what Query Store does is to capture information about your query execution. That is, you can use Query Store to find out where you bottlenecks are.

A recommended approach with upgrades like this is as follows:

Make sure Query Store is enabled.
Make sure that the compatibility level is the old level, in your case 130.
Run with this for a week or two.
Switch to the new compat level, in this case 160.

You can now use Query Store to find regressed queries, and you can also address queries by forcing the old plan from the older compat level through Query Store. This may not be a long-term solution, but it helps with the critical issue.

There is is also tooling support for this in SSMS. Right-click a database and select Tasks from the context menu, and at the bottom of the task menu there is Database Upgrade.

If you feel that you see performance degradation also with compat level 130, you will need to dig deeper in the Query Store data to find slow queries and think about how they can improved.

(*) There are however some Intelligent QP features that builds on Query Store. They analyse plans for certain patterns, and they can induce Query Store hints to affect cardinality estimation. So that way Query Stored can actually improve performance only by being on. But again, these are quite special cases and do not always happen. Also, these features are only available in Enterprise Edition.

Greg Booth 1,496 Reputation points

2026-02-19T09:33:53.48+00:00

Hi Erland

Thank you for replying to this question.

We are using Enterprise edition.

For three weeks after upgrading sql to sql 2022 ( from 2016), we left the database compatibility level at 130 ( 2016), but the performance was 10% slower than before.

i.e. with the database running as a 2016 database , the only difference being the sql executuables version, it was 10% slower.

All the other changes we have made since ( changing the database compatibility level to 160, enabling query store, changing the maxdop settings from the defaults) have made no difference.

Kind regards

Greg
Erland Sommarskog 133K Reputation points MVP Volunteer Moderator

2026-02-19T21:20:17.3733333+00:00

Had you had Query Store enabled already when you were on SQL 2016, there would have been some data you could have worked from to narrow down more precisely where the slowness occur.

Without that information... Well, you can still use Query Store to find slow queries and try to improve performance of these queries.

What do these procedures look like? Mainly set-based statements? Or are there lots of loops with single-row processing? I'm seem to recall that some people have observed a slowdown for plain T-SQL statements that runs in a loop without any table access. But I have no idea if that is applicable to your system.
Greg Booth 1,496 Reputation points

2026-02-24T12:37:39.2466667+00:00

Thanks Erland

The procedures are set based statements.

This is only the test system, so we will turn on querystore in live before we upgrade.

But will use querystore in test first to try some tuning.
Erland Sommarskog 133K Reputation points MVP Volunteer Moderator

2026-02-24T21:59:23.3333333+00:00

Yes, that is a good plan. Turn on Query Store in production and run for a while, and then make a new upgrade test.

One thing I did not ask: did you do an in-place upgrade? Because if you don't have the same version of OS and the same hardware, that could very well account for the difference. Since you say that this is a test, I guess you copied from production. You should probably run critical operations in the test environment before the upgrade, so that you have a benchmark.
Greg Booth 1,496 Reputation points

2026-02-24T23:13:56.88+00:00

Hi Erland, yes this is an inplace upgrade, so the same OS and hardware as before. The OS is still server 2016, we will be upgrading this too soon. We have been running a test ssis load on this server for several years, and i have the data for how long these runs take from before the sql version upgrade. Its still bothering me slightly that when post upgrade ( for a few weeks), i did not change the database compatibility level ( i.e. left it as 2016 level - 130), and the overnight workload takes 10% longer ( approx 30 mins longer) than it did before. The only change at that point was the sql 2022 executables. We might try giving the box more resources....

Answer 2

Lakshmi Narayana Garikapati 920 Microsoft External Staff Moderator

Hi Greg Booth,

Thank you for reaching out to the SQL Q&A forum. I have included the previously resolved thread related to your issue, which may help you better understand and resolve the problem.

https://learn.microsoft.com/en-us/answers/questions/5536165/performance-degrade-after-migrating-to-sql-server?page=0#answer-12207520

Thanks,

Lakshmi.

Lakshmi Narayana Garikapati 920 Reputation points Microsoft External Staff Moderator

2026-02-20T08:09:09.29+00:00

Greg Booth ,

I'm following up to check whether your issue has been resolved. If you still have any questions or need further assistance, please don't hesitate to reach out we're happy to continue supporting you.

We truly appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi
Lakshmi Narayana Garikapati 920 Reputation points Microsoft External Staff Moderator

2026-02-24T09:00:35.11+00:00

Greg Booth ,

I'm following up to check whether your issue has been resolved. If you still have any questions or need further assistance, please don't hesitate to reach out we're happy to continue supporting you.

We truly appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi

Share via

how to resolve degraded sql performance since upgrading from sql 2016 to sql 2022

1 additional answer

Your answer