Change SQL Server DBs compatibility levels on live Prod?

Question

Hello, we just migrated old DBs from SQL 2014 to new SQL 2016.

Is it safe/ok to change SQL Server DBs compatibility level on live Prod from lower version SQL 2014 (110) to SQL 2016 (130) or are there any concerns/negative impacts to consider?

Thanks in advance.

Answer 1

In answer to your question, yes you can change it on Prod at any time, 100 times a day if you would. It does not hurt the server.

For the specific things it controls, see:

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

Answer 2

There might be. Or there might not be.

Two things can be problematic for you:

• Breaking behavioural changes. (There are some subtle ones, and which have bitten some people.)
• Performance regressions. (It is the nature of optimization that was is good for the majority can prove to be bad for some people.)

As for the latter, enable Query Store in the database, and let it run with the old compat level for a week or so, and then flip. If you get performance regressions with a query or two, Query Store easilyi permits you to force the old plan as a temporary measure.

Answer 3

Hi,

SQL 2014 (110) to SQL 2016 (130)

There is a VERY good reason why the compatibility version is not upgraded by default! It is up to you to check and confirm and test everything.

Compatibility 110 is NOT the default for SQL Server 2014 but for SQL Server 2012. I recommend to read more information regarding upgrading from 2012 and not just about upgrading from 2014.

What you are planning to do can have a huge impact, since there was huge change in SQL Server 2014 (compatibility 120 and above).

SQL Server is using a component name Cardinality Estimator (CE) which is responsible for predicting the number of rows that the query will return. The compatibility level determines which CE will be used.

Microsoft SQL Server 2012 and earlier versions (compatibility 110 and before) work based on an old CE which is the same as SQL Server 2000 (I am not familiar with versions before 2000).

SQL Server 2014 comes with a new designed CE with new and major updates on the assumptions and algorithms.

In addition, I assume that your server was upgraded from 2012 to 2014 which is why the database compatibility is 110. During such upgrade the master database compatibility level is not upgraded and continue to be 110. This mean that your new master database is probably using compatibility 130 and your migrating might be impacted but the change in the master compatibility.

Moving from compatibility 110 to above, usually result in a great improvement (using the new CE), but in some specific cases it can do the opposite when using the default setting.

Note! In SQL Server 2016 you have a new configuration which can help you. Instead of using the old compatibility you can now use LEGACY_CARDINALITY_ESTIMATION. using LEGACY_CARDINALITY_ESTIMATION can be done in the database scoped configuration, query hint, or via the Query Store hint feature. This will allow you retaining other improvements in the query optimizer of the new compatibility version, while using the old CE.

These options in 2016 are huge improvement as you can control the CE in different levels so different queries can use different CE. In your case (moving from compatibility 110 in SQL Server 2014 to SQL Server 2016) can be a great improvement if using correctly with the full power of this option.

Not recommended in most cases but if I already spoke about the controlling the CE, then you should know that Trace Flag 2312 can be used to force the new Cardinality Estimator usage and Trace Flag 9481 will force the old Cardinality Estimator usage.

Answer 4

Sorry I mistyped my original question, pituach is correct...we migrated from SQL 2012 (110) to SQL 2016 (130).

Thanks for all of the replies.

Answer 5

Hi @techresearch7777777 ,

In many cases, most user databases never had their compatibility levels changed after a migration to a new version of SQL Server. This usually didn’t cause any issues unless you actually needed a new feature that was enabled by the latest database compatibility level. You can change the compatibility level back to any supported level that you wish. But i suggest you reading this MS document before upgrading database compatibility level.

For the recommended workflow for upgrading the compatibility level, see Change the Database Compatibility Mode and use the Query Store. Additionally, for an assisted experience with upgrading the database compatibility level, see Upgrading Databases by using the Query Tuning Assistant.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".