Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 4 down vote accepted

I would recommend rebuilding indexes.

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

share|improve this answer
1  
Also rebuild stats. –  SuperCoolMoss Oct 21 '09 at 18:59
    
Yeah, we are going to rebuild indexes and update statistics and I will see what that does. –  duckworth Oct 21 '09 at 20:16
    
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 –  SuperCoolMoss Oct 22 '09 at 8:15

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.

share|improve this answer
    
Yeah, the performance problem is that it is using 10-12% more CPU for the same load. The throughput is the same. –  duckworth Oct 21 '09 at 20:14

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.

share|improve this answer

If it's 2 different servers then it could be Disk IO (raid settings?). I agree with @Massimo on the systax.

share|improve this answer

Compatibility levels should not be an issue.

If you haven't already done it, I would run PerfMon on that server to see if you can determine if the increased CPU utilization is coming from SQL Server, Windows, or something else.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.