One thing that a lot of DBAs, especially Accidental DBAs such as myself, don’t seem to realize is that just because a database is sitting on a specific version of SQL Server does not mean that database has all of the features of that SQL Server version enabled. To put it another way, you can have a database running on SQL Server 2014, but that database still be set to SQL Server 2008 compatibility and some of the features of 2014 will not be enabled.
Every database on a SQL Server has a Compatibility Level setting, which will enable some (not all) of the features of each version. For example, if you are on SQL Server 2014 and do not wish to use the new Cardinality Estimator on a specific database, you can simply set that database to SQL Server 2012 Compatibility Level and this will disable that 2014 feature (along with a few others). For a full list of what is enabled at each compatibility level see the Differences Between Compatibility Level sections of the MSDN Docs.
While being able to make this change is a handy feature to have, many people do not realize that it is a manual process, meaning that SQL Server will not automatically update the compatibility level of a database to match the server version when you put it on a new server. So, even if you migrated your databases to a new server with a higher version of SQL Server, the compatibility level of your databases will not change unless you explicitly go into the database and change the setting. The same thing applies if you do an upgrade to an existing SQL Server. I have seen many cases where databases were on a SQL 2014 or 2106 server, but the database compatibility level was still set to 2008.
There is one exception to this rule. If you restore a database that has a lower compatibility level than the new server allows, SQL Server will upgrade that database’s compatibility level to the lowest version that is allowed on that server. So, if your database is set at 2005 compatibility level, and you restore it to SQL Server 2016, the database’s compatibility level will be changed to 2008, because that’s the lowest version SQL Server 2016 will support. (NOTE: This does not mean that you can upgrade directly from SQL Server 2005 to SQL Server 2016. That is a different subject entirely. See Supported Version and Edition Upgrades.)
How Can I Find My Database’s Compatibility Level?
Finding the compatibility level for you database is pretty simple, you can do it by querying the sys.databases DMV.
|
1 2 3 4 5 6 7 8 9 10 |
-- Find Compatibility Level for a specific database SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; -- Find Compatibility Level for all databases on the server SELECT name, compatibility_level FROM sys.databases; |
How Do I Change My Database’s Compatibility Level?
Now before you go all willy-nilly and start updating your database compatibility levels, you need to make sure you test this change, ESPECIALLY if you are updating from a pre-2014 level to 2014 or higher. With the 2014 compatibility level come changes to the query engine that could cause significant performance degradation in some rare cases. (I’ve seen a 30 second query start taking 11 minutes) So, be sure you test, test, test this change before flipping the switch in production. You’ve been warned.
|
1 2 3 4 5 6 |
--change compatibility level to 2016 for your database ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 130; GO |
Notice that the compatibility level setting is actually a numerical value. That’s how Microsoft does this, each compatibility level has a numerical value assigned to it, and that’s what we use in the database settings. Be sure to check this list of Supported Compatibility Level Values for the version of SQL Server you are running.
This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.