Change Compatibility Level?

Question

Hi,

I have a database using Compatibility Level SQL server 2008 (100) running on a SQL server 2014 - can I change Compatibility Level to SQL server 2014 (120) without problems? Is it best practices to use the highest Compatibility Level? Any negativ impact?

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 1

That depends. You need to run the deprecated features DMV

select * from sys.dm_os_performance_counters
where object_name = 'MSSQL$INST03:Deprecated Features'
and instance_name = 'sysdatabases'

Ideally you would run the upgrade advisor on the database in its original SQL 2008 instance before your upgrade or moving the database.

Note that you might run into performance problems with the SQL 2014 compatibility mode.

DBCC TRACEON( 9481 );

Or keep the database in SQL2012 or below mode.

Answer 2

Here is some code that we used to set the compatibility mode from sql 2005 to sql 2008 using tsql code. Looking at the code it seems a bit much compared to the msdn page below. But it worked. You would have to change the 90 to 120 for SQL 2014.

ALTER DATABASE Compatibility Level (Transact-SQL)
https://msdn.microsoft.com/en-us/library/bb510680.aspx?f=255&MSPPError=-2147217396

I have not had clients go from 2008 to 2014. But have hundred go from 2005 to 2008 compatibility and several go from 2008 to 2012. Never had an issue. Maybe someone else will have 2008 to 2014 specific experience.

DECLARE @DBName VARCHAR(100)
SET @DBName = DB_NAME()
DECLARE @Compatibility_Level Int
SELECT @Compatibility_Level = compatibility_level FROM sys.databases  WHERE name = @DBName
IF @Compatibility_Level < 90
	BEGIN

		DECLARE @SQLSTATEMENT  NVARCHAR(MAX)
		DECLARE @AUTO_UPDATE_STATISTICS_ASYNC INT   
		DECLARE @Note VARCHAR(500)
		DECLARE @Note2 VARCHAR(500)
		DECLARE @Note3 VARCHAR(500)
		
		SET @DBName = DB_NAME()
		
		Set @Note = 'SQL Compatibility Mode for DB ' + @DBName + ' is ' + CAST(@Compatibility_Level as VARCHAR(10)) + ' and it must be >= 90 to run the 2014.01 installation script.'
		Print @Note	
		Set @Note = 'Compatibility mode will be changed to 90 (SQL 2005).'
		Print @Note	


		-- Retain auto_update_statistics_async value before setting to OFF
		SELECT @AUTO_UPDATE_STATISTICS_ASYNC  = is_auto_update_stats_async_on FROM
			sys.databases WHERE name = @DBName

		-- Set auto_update_statistics_async to OFF before Setting Single_User	
		SET @SQLSTATEMENT = 'ALTER DATABASE ' +  @DBName + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF'
		EXECUTE sp_executesql @SQLSTATEMENT

		-- Set to Single_User mode 													
		SET @SQLSTATEMENT = 'ALTER DATABASE ' +  @DBName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'				
		EXECUTE sp_executesql @SQLSTATEMENT
		
		-- Set Compatibility mode to 90		
		DECLARE @SQLVers NUMERIC(4,2)
		SELECT  @SQLVers = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR), 4)
		IF @SQLVers < 10    
			EXEC sp_dbcmptlevel @DBName,90			
		ELSE
			BEGIN
			SET @SQLSTATEMENT = 'ALTER DATABASE ' +  @DBName + ' SET COMPATIBILITY_LEVEL = 90'				
			EXECUTE sp_executesql @SQLSTATEMENT	
			END			
		
		-- Set back to Multi_User mode		
		SET @SQLSTATEMENT = 'ALTER DATABASE ' +  @DBName + ' SET MULTI_USER'				
		EXECUTE sp_executesql @SQLSTATEMENT		
					
		-- Set ASYNC back to original value														
		SET @SQLSTATEMENT = 'ALTER DATABASE ' +  @DBName + ' SET AUTO_UPDATE_STATISTICS_ASYNC'				
		IF @AUTO_UPDATE_STATISTICS_ASYNC = 0
			SET @SQLSTATEMENT = @SQLSTATEMENT+ ' OFF';
		ELSE	
			SET @SQLSTATEMENT = @SQLSTATEMENT + ' ON';
		EXECUTE sp_executesql @SQLSTATEMENT
		
		Set @Note = 'Compatibility mode was successfully changed to 90 (SQL 2005).'
		Print @Note			
	
	END
		

Answer 3

I am sure you have non-prod environment too and before upgrade you would have run upgrade advisor too.

I would suggest first change that in non-prod environment and go for end to end application functionality level test too before changing on prod.

If all go fine then you can do the same in all your environment.

More details:

https://msdn.microsoft.com/en-us/library/bb510680.aspx

Some issue may come at application level like below one or other:

http://dba.stackexchange.com/questions/60449/implications-of-changing-compatibility-mode-in-sql-server-from-100-to-110

---

Santosh Singh

Answer 4

You can change the Compatibility Level whenever you want.  The setting only controls how certain user COMMANDS function.  It does NOT change anything within the SQL Server or the database itself.

Answer 5

...but if your application(s) uses some commands which has been removed or changes behavior with the higher compat level, then you will have problems. You can change compat level back again, of course. These things is why you want to check with whoever wrote the app what compat level the app expects 

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 6

Hi,

I have a database using Compatibility Level SQL server 2008 (100) running on a SQL server 2014 - can I change Compatibility Level to SQL server 2014 (120) without problems? Is it best practices to use the highest Compatibility Level? Any negativ impact?

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

I usually ask the application team do to complete test to see their applications and codes,schema,dependency logic of their code or vendor recommendation to see -they are fit to higher compatibility,some hard codes apps it wont work in higher & they depends on the current -for this they have to make other plan to see how it can be fixed.

you & your application team should work both the side.

---

Regards, S_NO "_"

Answer 7

Database compatibility level mostly affect SQL syntax and query parsing, and it should have no impact on performance. See here: http://www.sqlmvp.org/sql-server-database-compatibility-level-performance/

Answer 8

Jason - there are some documented issues.

https://blogs.msdn.microsoft.com/bharry/2016/02/06/a-bit-more-on-the-feb-3-and-4-incidents/

Answer 9

Database compatibility 2014 gives us the new Cardinality Estimator ("CE 13"), which to a very high degree can affect performance (since because of the new way to calculate number of rows from operators can affect execution plan choice).

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 10

Yes, You can change the compatibility level any time. But there are multiple things you need to consider :-

1) Is your database design and code is compatible to 2014.

2) Is your database design and code is ready to work with new cardinality of 2014.

There may be a case your application and DN design is not ready for above points and application start facing issues.

You can also use upgrade advisory for detailed report. 

I suggest to check with your application vendor and test results on NON PROD before any decision. 

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 11

Hi,

I have a database using Compatibility Level SQL server 2008 (100) running on a SQL server 2014 - can I change Compatibility Level to SQL server 2014 (120) without problems? Is it best practices to use the highest Compatibility Level? Any negativ impact?

I see a big issue and that is CE regression. I have see many such issues where when you change compatibility level to 120 SQL Server starts using new CE and prepares a bad plan sometimes very bad plan and you are just rendered clueless what is happening.

Although you can immediately revert the compatibility level, but I first suggest you to test test and test your application after changing compatibility level and when you are satisfied with result only then move ahead.

There is also way to mitigate it after changing to 120 compatibility level

1. Make sure you are on Latest SP and CU

2. Make sure you have enabled TF 4199 globally. 

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP