Overview
In this real world scenario I was troubleshooting some performance tuning issues that were identified in testing following a database server migration from SQL Server 2000 to SQL Server 2005. One of the things we checked was the compatibility mode of the affected database. I discovered that the compatibility level of the database in question was set to ‘80’ – which corresponds to SQL Server 2000. We can use the
sp_dbcmptlevel stored procedure to display the current compatibility level of the current database or set the compatibility level for a specified database to a new level. Per SQL Server Books Online - “Transact-SQL provides the
sp_dbcmptlevel stored procedure that sets certain database behavior”. Compatibility mode is configurable for each database,
not at the database server level. Any
new databases are set to the value specified in the
model system database.
How to Use the Stored Procedure
Note: Only the database owner, members of the sysadmin fixed server role, and the db_owner fixed database role (if you are changing the current database) can execute this procedure.
The following T-SQL code displays the current compatibility level for the tempdb system database:
exec sp_dbcmptlevel tempdb
The following is the result of the previous query:
The current compatibility level is 90.
The following T-SQL script creates a new database testdb, displays the current compatibility level for the database, and sets the compatibility level to a new value using the @new_cmptlevel optional parameter and drops the database.
create database testdb
go
-- display current compatibility level
exec sp_dbcmptlevel testdb
go
-- set new compatibility level
exec sp_dbcmptlevel 'testdb', '80'
go
-- display new compatibility level
exec sp_dbcmptlevel testdb
go
--drop the database
drop database testdb
The following is the result of the previous script:
The current compatibility level is 90.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The current compatibility level is 80.
What are the various compatibility levels?
For databases up through SQL Server 2005, the following chart contains the possible compatibility levels.
|
60
|
SQL Server 6.0
|
|
65
|
SQL Server 6.5
|
|
70
|
SQL Server 7.0
|
|
80
|
SQL Server 2000
|
|
90
|
SQL Server 2005
|
What is the impact of changing database compatibility from SQL Server 2000 to SQL Server 2005?
Here is a partial list of the items affected by setting SQL Server compatibility from 80 (SQL Server 2000) to 90 (SQL Server 2005):
1) For locking hints in the FROM clause, the WITH keyword is no longer optional – you may have to fix some queries that will break.
2) You now have access to certain reserved keywords specific to SQL Server 2005 such as Pivot and Unpivot
3) Certain Order By expressions will work differently
4) Query plans will be compiled to optimize running stored procedures under SQL Server 2005
Best Practices for Setting Compatibility Mode
Changing compatibility mode for a specified database while users are connected to a database may produce inconsistent results. In one possible scenario where a procedure is running while the compatibility mode is being altered, the compiled plan might be based on both the current and the new compatibility modes. This may cause further problems if the plan is placed in plan cache and reused for future queries. Consider the following steps to modify compatibility mode in a Production environment:
1) Set the target database to single user mode using ALTER DATABASE
2) Set the compatibility mode of the target database to the new level
3) Put the target database in multi user mode using ALTER DATABASE
