Compatibility level of SQL Server

Question

Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?

Answer 1

To know the current compatibility_level of your database you can run:

SELECT name,compatibility_level 
FROM sys.databases 
WHERE name = 'DatabaseName';

Result for my TEST database:

name    compatibility_level
TEST    140

If the compatibility level changes, for example with the command:

ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 120;

You could find the date and time that happened in the sql server error log:

Or reading the (current) error log file with xp_readerrorlog:

EXEC sp_readerrorlog 0, 1, 'COMPATIBILITY_LEVEL' 

Result

Replace the first parameter of xp_readerrorlog to 1 to read the second error log file, 2 for the third file, ... .

Answer 2

Additionally, you can verify that your database isn't running with the down-level Query Optimizer (LEGACY_CARDINALITY_ESTIMATOR) by running the following statement:

use [yourDB]
GO
select * from sys.database_scoped_configurations

This will give you the following result set on a SQL Server 2014 instance for a given database which is running with the legacy cardinality estimator:

  configuration_id |             name              | value | value_for_secondary 
 ------------------|-------------------------------|-------|--------------------- 
                 1 | MAXDOP                        |     0 | NULL                
                 2 | LEGACY_CARDINALITY_ESTIMATION |     1 | NULL                
                 3 | PARAMETER_SNIFFING            |     1 | NULL                
                 4 | QUERY_OPTIMIZER_HOTFIXES      |     0 | NULL         

I am adding this information as an answer, because you can have the instance running with the current compatibility level of 120 (for SQL Server 2014), but the database will still be using the old cardinality estimator, which negates some of the benefits of using the newer SQL Server 2014 compatibility level for your databases.

Reference Material

• Cardinality Estimation (SQL Server) (Microsoft | SQL Docs)
• ALTER DATABASE (Transact-SQL) Compatibility Level
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• DBCC TRACEON - Trace Flags (Transact-SQL) (specifically TF 2312)
• Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level