Performance degradation after migrating from SQL Server 2016 to SQL Server 2022

Question

I have migrated our load test environment from SQL Server 2016 to SQL Server 2022 (Developer edition).

Here are some findings:

1. Changed compatibility level of databases to 160 (for SQL Server 2022) - performance degraded by ~30% (changing back to 130 makes things as they are)
2. Disabled Resource Governor (with comp level = 160) - performance degradation ~ 27%
3. Changed 'Legacy Cardinality Estimate = ON' (with compatibility level 160) - performance becomes as good as it is with SQL Server 2016

After getting data in Query Store (comp level 130 vs 160), there are queries with so much execution time variation. even they are using same execution plan.

Screenshot of top 25 queries with max. variation:

Screenshot of top 25 Queries with max variation

Any suggestions on how we can get same performance with new compatibility level? Any setting which could be causing this issue?

• Doug Deden: While you wait for answers, this might give you some ideas: brentozar.com/archive/2019/05/…
• Yannick Liekens: Are you seeing any different wait types between old/new compatibility level? (Can also be seen in querystore). If you have the option of manually executing the queries, try to run a query in old and new compatibility level, grab the actual plans and compare those.
• sepupic: @user3510083 When you say "migration" do you mean not upgrade but database transfer to another server (with may be different RAM, CPU, disks)?
• user3510083: it is upgrade actually . we did an In Place Upgrade from SQl version 2016 to SQl 2022

Answer 1

First of all if you came from an old compatibility version of your database, changing for a brand new, you need to update all the optimizer stats in the FULLSCAN mode.

This can be done by this TSQL command:

DECLARE @SQL NVARCHAR(max) = N'';
SELECT SQL = CONCAT(@SQL, 
 N'UPDATE STATISTICS ON ', 
 QUOTENAME(s.name), '.',
 QUOTENAME(o.name), ' WITH FULLSCAN;')
FROM sys.objects AS o 
 JOIN sys.schemas AS s
 ON o.schema_id = s.schema_id
 JOIN sys.indexes AS i
 ON o.object_id = i.object_id
WHERE o.type in ('U', 'V')
 AND i.index_id < 2;

You did not provide which compatibility mode the database was...

• user3510083: we were in 130 previously . will try updating stats and see if that works . thanks a lot for your response.
• Yannick Liekens: There's no reason you MUST do a fullscan after a change to a new compatibility. If you didn't run in FULLSCAN in previous version, you might not need it now. Default sample rate is good for most workloads.