asked
SQL2008 Performance slower than SQL2000
We are trying to upgrade a database from SQL2000 to SQL20008R2. We copied the database to a new server that has the same # processors and same amount of memory as the old server, but the new server is SQL2008R2 and the OS is 2008R2. The application and programs are very old, so we are keeping the database in SQL2000 compatibility. The developer is testing a batch program on the new server, and it is taking 3 times longer to run on the new server. He is using the SQL2008 drivers. The CPU is only about 50% used whil the program is running. Any ideas why it is taking so much longer on 2008? Are there any other parameters we should be looking at?
First things first, after the update, you'll have either manually rebuild all statistics or wait while the system rebuilds each set of statistics as it comes to them. I'd suggest the former and not the latter. All things being equal, usually, you should see some improvement in performance. But... if you have complex queries, especially queries that were iffy in 2000, they frequently run very poorly in 2008. If this is the case, I'd suggest getting a look at the execution plans of the queries and see if there is anything you can do to improve them, add an index, something. If it's not the case, then I'd suggest checking the wait stats to see what is causing things to run slow on the server. Maybe there's a setting that's off somewhere.
I realize I'm answering an older post and I hope I'm not too late but, as a bit of a side bar, I guess my answer would be... If you're going to keep the database in the SQL Server 2000 compatibility mode, then why even bother with the migration? If you ever update to SQL Server 2012 or higher, then you'll be in deep Kimchi because there won't be a compatibility mode for 2000 there. If you intend to not upgrade the old programs or the old application, then keep the 2000 server running and call it a day.
16 People are following this question.
