Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2014-08-14 : 09:28:01
|
| Hi folks,Back some years ago when we switched from SQL 2005 to 2008R2, I noticed that SQL2008R2 is much slower. Databases were upgraded from compatibility level 90 to 100, so that's not it. Is this normal? Thanks! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-14 : 12:45:43
|
| 2008R2 is not slower than 2005 assuming resource requirements are met. Also, how did you do the upgrade? Did you move things around, change indexes, anything like that? |
 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2014-08-14 : 15:10:48
|
| Only used ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100. Should I be doing more? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-14 : 15:13:08
|
| probably a lot more! See here: http://technet.microsoft.com/en-us/magazine/gg454217.aspx |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2014-08-14 : 16:45:10
|
| Thanks for that article. But these are very simple databases which meet the upgrade criteria as per the Upgrade Advisor. All I did was detach the DBs from the old x32 station which hosted the SQL2005Express, reattached them on the x64 station hosting the SQL2008R2Express, and then altered the compatibility level. Everything works well but it doesn't seem quite as nimble as it was. Perhaps SQL2005 is just plain lighter and less resource-intensive than SQL2008R2... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-14 : 17:23:34
|
| You should run update stats on all indexes after an upgrade.SQL 2005 is not lighter than 2008 or 2008 R2.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-08-15 : 02:14:24
|
| You've moved the databases to another server. Have you checked the newer server is set up in a similar way? Are you maintaining the databases in a similar way?Is memory on the server the same? etcCompatibility - level is a feature useful to exploit , particuarly after an upgrade. As you move the compatibility level up - it restricts usage of some specific older features, but I've never experienced slower performance.http://www.sqlserver-dba.com/2014/01/compatibility-level.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
SQL2008 slower than SQL2005