Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
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;
Or reading the (current) error log file with
EXEC sp_readerrorlog 0, 1, 'COMPATIBILITY_LEVEL'
Replace the first parameter of
xp_readerrorlog to 1 to read the second error log file, 2 for the third file, … .
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.
- 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