RDS SQL Server slow after migration

After migrating your RDS SQL Server from 2019 Enterprise Edition to 2022 Standard Edition, it's not uncommon to experience some performance changes. While you've mentioned that CPU usage is below 70% and no latency is observed, the slowness in operations like altering views could be due to several factors:

1. Edition change: Moving from Enterprise to Standard Edition might have removed some performance-enhancing features that were previously available.

2. Version upgrade: SQL Server 2022 introduces new features and changes that might affect query execution plans and overall performance.

3. Resource allocation: Ensure that your RDS instance type provides sufficient resources for your workload, especially memory and I/O capabilities.

4. Query plan changes: The new version might be using different query execution plans, which could impact performance.

5. Configuration settings: Some default settings in SQL Server 2022 might need adjustment for optimal performance.

To address these issues, consider the following steps:

1. Review and optimize your queries, especially those related to view alterations.

2. Check if the compatibility level of your database is set appropriately. For SQL Server 2022, the default is 160.

3. Monitor Performance Insights, Amazon CloudWatch metrics, and Enhanced Monitoring metrics to identify any resource constraints or bottlenecks.

4. Evaluate if you need to increase the size of your RDS instance to better handle the workload.

5. If possible, enable and review query store data to identify queries that may have regressed in performance after the migration.

6. Consider using Accelerated Database Recovery (ADR) feature in SQL Server 2022, which can improve database availability and transaction rollback speed.

7. If the slowness persists during specific maintenance windows or periods of heavy load, you might want to reschedule these operations for times of lower activity.

If these steps don't resolve the issue, you may need to perform a more in-depth analysis of your specific workload and database configuration to identify the root cause of the slowness.
Sources
RDS Sql Server Instance extremely slow - useable | AWS re:Post
Amazon RDS for SQL Server now supports SQL Server 2022 | AWS Database Blog
Troubleshooting target latency issues - AWS Database Migration Service

Hi,

Migration of SQL Server 2019 to 2022 has some pitfalls on performances.

I'd suggest you to read:

• https://learn.microsoft.com/en-us/answers/questions/1182704/sql-server-2022-transaction-performance
• https://www.brentozar.com/archive/2019/01/which-version-of-sql-server-should-you-use/
• https://blog.sqlauthority.com/2020/02/10/sql-server-top-reasons-for-slow-performance/

They should help you in tuning your SQL Server 2022 configuration to achieve again good performances.

Best,

Didier