What’s the issue?
Every SQL Server database has a compatibility level setting that controls which version of the query optimizer and which T-SQL behaviors apply to that database. The compatibility level is expressed as a number tied to a SQL Server version (for example, 150 for SQL Server 2019, 160 for SQL Server 2022). When a database is restored or attached to a newer SQL Server instance, its compatibility level does not automatically change, which is why databases often run at a level lower than the host instance supports.
Why is this a problem?
Running at a lower compatibility level means the database is not benefiting from improvements in the query optimizer, intelligent query processing features, and T-SQL enhancements introduced in newer versions. Features such as adaptive joins, batch mode on rowstore, table variable deferred compilation, scalar UDF inlining, and parameter sensitive plan optimization are tied to specific compatibility levels and will not activate on older settings.
Beyond performance, lower compatibility levels also lock the database out of newer T-SQL syntax and behaviors that developers may want to use. Older compatibility levels (specifically 100 and below) are also deprecated and will eventually be removed, creating a future upgrade obstacle.
What should you do about this?
Identify affected databases by querying sys.databases and reviewing the compatibility_level column against the host instance version. Before changing the level, capture a performance baseline (key query durations, plan cache contents, wait statistics) so you can compare results after the change. Use Query Store, if enabled, to make the change safer by allowing you to revert plan regressions, and consider enabling QUERY_STORE first if it is not already on. Apply the change with ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL =
Test thoroughly in a non-production environment first, especially for vendor applications that may certify only specific compatibility levels. After the change, monitor query performance and watch for any regressions, using Query Store to force older plans if specific queries degrade.
Read more…
View or Change the Compatibility Level of a Database – SQL Server | Microsoft Learn