MSSQL Compatibility modes
Posted: Thu Jun 04, 2009 7:27 pmRunning SQL 2008 or 2005 ? Did you upgrade from a previous version of Microsoft SQL Server at any time ?
If you answered yes to both of the above questions, then chances are you aren't getting the most out of your SQL Server in terms of performance and features.
Fortunately, a single setting needs to be changed to remedy this.
When you restore a database on MSSQL, if the backup was from a prior version, then that database is automatically placed into a compatibility mode with the original version. This was a defensive move by Microsoft to make sure upgrades had no nasty side-effects. However, there are quite substantial performance gains to be had by running the database in the same compatibility mode as the server.
To check the compatibility mode, the easiest way is to open management studio, right click on the database in question, choose properties and you'll be presented with the database properties dialog.
Choose the Options page on the left, and thn you'll see the Compatibility Level as per this screen shot :
If the level does not match the SQL Server version, then change it so it does!
If you answered yes to both of the above questions, then chances are you aren't getting the most out of your SQL Server in terms of performance and features.
Fortunately, a single setting needs to be changed to remedy this.
When you restore a database on MSSQL, if the backup was from a prior version, then that database is automatically placed into a compatibility mode with the original version. This was a defensive move by Microsoft to make sure upgrades had no nasty side-effects. However, there are quite substantial performance gains to be had by running the database in the same compatibility mode as the server.
To check the compatibility mode, the easiest way is to open management studio, right click on the database in question, choose properties and you'll be presented with the database properties dialog.
Choose the Options page on the left, and thn you'll see the Compatibility Level as per this screen shot :
If the level does not match the SQL Server version, then change it so it does!