5

Link: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-ver15

I am migrating sql server from 2016 to 2019. I understand that, by default, post migration the databases will retain the existing compatibility level. Upgrading the compatibility level enables new features.

Microsoft recommends following below approach to upgrade the compatibility level to avoid regression:

enter image description here

Does the compatibility level upgrade result in any breaking results? Or is query regression (which I think means query taking more time than usual due to bad execution plan selection) the only negative side effect of the compatibility upgrade?

3 Answers 3

6

Query performance regression is the main concern, and the reason why Microsoft recommends first enabling Query Store before changing Compatibility Levels. This allows Query Store to build a performance baseline of your main queries before you change the Compatibility Level to be able to compare after when you change it so that it can provide you information and metrics on which queries regressed (got slower). As the docs mention, you can then use Query Store to force the "last known good plan" as a short-term fix, while you analyze how to optimize that query with a better long-term fix.

Of course it's always possible to run into bugs from changing the Compatibility Level, but I think that's less likely than running into a bug from the actual instance version upgrade. For example, when I upgraded from 2016 to 2019 RTM recently, I ran into a bug with ownership chaining with a Function that was being called across a Linked Server. That was resolved by installing the latest CU at the time which is currently CU 15. So it's very important you patch your 2019 instance to the latest CU to minimize any bugs.

Also this is why it is recommended to do a version upgrade in your development server first, wait a few weeks or months to analyze the outcome, and then apply the same upgrade in production after sufficient testing.

3

I'd also like to add to the other replies, since you are going from 2016 to 2019, that there has since 2016

Above are, of course, only what is documented.

1
  • I have couple of dbs with compatibility level from sql 2012 (when they were migrated from 2012 to 2016) so understanding the implications to plan better.
    – variable
    Apr 13 at 13:30
2

Yes, upgrading the compatibility level can cause breaking results.

Here's an excerpt from the Using compatibility level for backward compatibility doc:

Breaking changes introduced in a given SQL Server version may not be protected by compatibility level. This refers to behavior changes between versions of the SQL Server Database Engine. Transact-SQL behavior is usually protected by compatibility level. However, changed or removed system objects are not protected by compatibility level.

An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. Under database compatibility level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. To restore previous conversion behavior, set the database compatibility level to 120 or lower.

Examples of breaking changes not protected by compatibility level are:

  • Changed column names in system objects. In SQL Server 2012 (11.x) the column single_pages_kb in sys.dm_os_sys_info was renamed to pages_kb. Regardless of the compatibility level, the query SELECT
    single_pages_kb FROM sys.dm_os_sys_info will produce error 207
    (Invalid column name).
  • Removed system objects. In SQL Server 2012 (11.x) the sp_dboption was removed. Regardless of the compatibility level, the statement EXEC sp_dboption 'AdventureWorks2016', 'autoshrink', 'FALSE'; will produce error 2812 (Could not find stored procedure 'sp_dboption').

Relevant content: Discontinued database engine functionality in SQL Server.

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.