Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to upgrade our SQL Server version from SQL Server 2008 to SQL Server 2008 R2. But when I restored a SQL Server 2008 backup file in SQL Server 2008 R2, it's significantly degraded the performance of stored procedures. I took a full back up from old SQL Server 2008 and restored it in new SQL Server 2008 R2. I checked the execution plan of several stored procedures to make sure no indexes are missing, but everything is same.

Can anyone please give me an advice about it if I need to do any further steps to get better performance on it?

Thanks, Dony

share|improve this question

migrated from stackoverflow.com Apr 7 '13 at 4:11

This question came from our site for professional and enthusiast programmers.

    
List the execution plan from both versions for the same query on a machine with the same load. –  Ali Razeghi Apr 7 '13 at 4:37
2  
First thing after a database upgrade should be to update your statistics and rebuild all the indexes. And then start from there. Search the site, I'm sure you'll find more tips for a successful upgrade. Don't migrate in production unless you test with same loads and are getting the same or better performance (use profiler traces, use load tests, but try to cover most bases). Don't let it hit you when least expected! –  Marian Apr 7 '13 at 11:01

1 Answer 1

The odds are that on the restore, it is appropriate to update statistics on the db. In many cases, this step alone will help.

If it does not help, it may be that index fragmentation crept up and became an issue now, though my $ is on statstics...

share|improve this answer
1  
You should give some details about how to perform your suggestions, and give some background about why they may help. –  Max Vernon Apr 7 '13 at 18:27

Your Answer

 
discard

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