0

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance.

Many backups (like full, differential, and log) will be taken while using the new compatibility level 90 or 100. This database is also configured with log shipping.

My question is can I change compatibility level back to 80 safely without threats or must I take any considerations?

Improve this question
2
  • 1
    Yes, you can change it back if you need to, but you should try to solve any individual regressions that you have proven are really due to compat level and not due to different plan/stats after a recompile rather than going back wholesale to a 20-year old compat level IMHO. This does not affect backups except that, if you back it up while it is in 90 or 100, then restore that backup, the restored copy will also be in 90 or 100. Related.
    – Aaron Bertrand
    Jun 22, 2020 at 18:49
  • Performance regression with compatibility levels 110 and less are not very common. It's a different story once you move to a supported SQL Server version where one should consider the implications of the new cardinality estimator in levels 120 and higher along with database scoped configuration options.
    – Dan Guzman
    Jun 23, 2020 at 10:13

1 Answer 1

Reset to default
0

It's quite easy to change it back and forth. But you will have to test the performance. Here's a post on how to change it in SSMS.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.