Would the database compatibility level affect performance?

Question

We just migrated our Microsoft SQL Server from 2005 enterprise to 2008 standard on the same specification hardware. The databases were backed up and then restored to a clean sql 2008 install. The compatibility level is currently at 90 (2005). None of the Enterprise features were being used on 2005 (thus the edition downgrade). We have noticed about a 10-12% CPU utilization increase on 2008 compared to 2005. The only other change was that the SQL 2005 was running a server 2003 x64 and 2008 is running server 2008 x64. We aren't using any full text indexes.

Would the database compatibility level affect performance? We haven't rebuilt any indexes or updated statistics, would that have an impact? What else should we look for that could be affecting performance?

Solution 1

I would recommend rebuilding indexes.

https://stackoverflow.com/questions/1531372/should-i-rebuild-table-indexes-after-a-sql-server-2000-to-2005-database-migration

Solution 2

Is there a performance problem? In other words the only thing you are seeing is an increase in CPU utliization but not a decrease in transaction rate?

If the application is now capable of ( and processing ) more transactions per sec, it's entirely possible that the increase in processor time is due to higher thruput. If there are hardware differences, that could be the cause as well. You should update stats and indexes on the database after a move, and that could cause an increase in CPU ( but it depends on the application)

Compatibility level only refers to the T-SQL language conmpatability level so generally there isn't a performance hit unless you are using some code that has been superceded by a far superior method, in which case the engine might not be as optimized for the old code.

Solution 3

You can find documentation about the different compatibility levels here.

Database compatibility level mostly affect SQL syntax and query parsing, and it should have no impact on performance; anyway, if you're not forced by application issues to use a previous level, upgrading it is considered best practice.

Comments

[Tanaji Kamble]

Yeah, the performance problem is that it is using 10-12% more CPU for the same load. The throughput is the same.

[Tanaji Kamble]

Yeah, we are going to rebuild indexes and update statistics and I will see what that does.

[SuperCoolMoss]

You should be running index/stat maintenance regularly. Here's a couple a couple of excellent custom scripts which can do this on SQL2005/2008: sqlfool.com/2009/06/index-defrag-script-v30 ola.hallengren.com/#1227266