Hi @Arash Masroor ,
Which version of SQL Server are you upgrading to?
Generally speaking, whether to change the compatibility level is not the main issue, but unless the compatibility level of the database is changed, some functions will not be available. When migrating from an older version of SQL Server to SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression.
The classic scenario for why you might want to use an older database compatibility level after an upgrade to a newer version of SQL Server is to use it as an interim migration aid to work around some application compatibility issues that might surface after a migration (which you hopefully discovered during your pre-migration testing efforts). After further testing and possible database/application changes, it is pretty common to eventually change the database compatibility level to match the database engine version of the instance, which lets you take advantage of some of the new features and behavior differences in that version.
If you upgrade SQL Server to 2016 and higher, you can use Query Store to find and correct query plan regressions.
And SQL Server 2017 adds automatic plan correction which leverages Query Store to try to force the last known good plan for query plans that have performance regressions.
Please refer to Upgrading SQL Server–Diagnosing Performance Regressions and ALTER DATABASE (Transact-SQL) Compatibility Level for more details.
In addition, please avoid posting the same question repeatedly, thanks for your understanding and support.
Best Regards,
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.