Change compatibility

Question

is there any impact on always on while change the compatibilkity level.

 

Answer 1

For Always On I can't think of anything where the compatibility level can matter.

However, for query execution there can be surprises. There have been many changes in the optimizer since SQL 2008, so many of your queries may get a new execution plan. In many cases, performance will improve, but you may not notice. However, there can also be regressions where one or two queries start to run painfully slow.

In order to prepare yourself for this, I recommend that you turn on Query Store, if you have not done this already. Let the database run for one or two weeks with Query Store on and the old compat level. Then flip the switch. If there is a query that regresses, you can force the old plan to solve the urgent problem.

Often when a query regresses like this, there is some problem with it. Maybe it is poorly written in some way. Maybe the indexes available are not the best for the query. With a little luck, the query ran well in the old compatibility mode, but such luck does not always last when you upgrade.

In SSMS, right-click the database, select Task, and at the bottom of this menu you find Database Upgrade, which helps you with using Query Store as I described it above.

Answer 2

Hi @ Allen Anoushei,

Changing the compatibility level should not directly impact the Always On availability groups. However, it's recommended to perform the change during a maintenance window and ensure that all replicas are synchronized before making the change. Additionally, test the failover process to ensure that it works as expected after the compatibility level change.

But changing the compatibility level can affect query performance. SQL Server 2019 introduces new query optimization features that may change the execution plans for your queries. It's important to test your workload under the new compatibility level to identify any performance regressions. Query store is a better tool as Erland mentioned. Please read MS document Change the database compatibility level and use the Query Store to get more detail information.

Some features available in SQL Server 2019 may not be available or behave differently under the 2008 compatibility level. Upgrading the compatibility level allows you to take advantage of new features and improvements.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.