5

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?

5

I would recommend rebuilding indexes.

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

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.

  • 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
1

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.

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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