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.

We have a 30 GB Database on SQL Server 2008 R2 Standard Edition. Yesterday we backed up the database and restored it to a different Virtual Machine which has exactly the same specs as the previous machine - The only exception being the new VM has a lot more RAM.

However we found that the performance of the older VM was much better than this new VM. My questions are:

  1. Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time).

  2. Is there any other obvious step which we are missing - We don't have an in-house DBA as the work we do on the database front is not much.

share|improve this question

migrated from stackoverflow.com Mar 26 at 5:59

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

    
I believe backup/restore puts the indexes in the exact same state as the original DB. What about the IO performance? Are you using comparable storage hardware? Did you compare cold startups, or had the old VM been running for awhile already? –  mellamokb Mar 25 at 23:57
    
There is a reported issue when CDC is enabled a restore can cause slow performance on new machine. but since you are on standard edition I dont think you can possibly have CDC enabled as standard edition does not have this feature. –  M.Ali Mar 26 at 0:14
    
See if this :dba.stackexchange.com/questions/53726/… helps you. –  Kin Mar 26 at 12:03
add comment

3 Answers 3

It depends on if the database is going to be used for critical apps and if you have a maintenance window where you can perform this (considering the size of the database) or you could just re-index few important tables. It depends on the nature of your data and its use.

share|improve this answer
add comment

Have a look at the Maintenance Plan Wizard. It will help you create a series of scheduled jobs that will re-index the db and update statistics, etc. It can also shrink the db, which may be the issue you are having after the restore - the transaction log could be very large.

share|improve this answer
add comment

There are a lot of SQL Server settings that can affect performance outside of the server hardware specs. The indexes and statistics are restored as part of the database restore, so the fact that you just restored yesterday leads me to believe that is not the problem (at this point in time, but could become a problem in the future if not addressed). There are maintenance jobs that can rebuild indexes, also check your old server and see of there were any maintenance jobs that were setup that need moved to the new server. Compare the SQL Server configs between the two servers using

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

Note: The last sp_configure statement just shows configuration output and does not actually change any server config settings.

Also check the disk setups between the two servers, including the RAID types and disk speed. Make sure that your database and logs files are on separate disks which are also separate from your OS drive. Check the tempdb setups between the two servers as well. The recommendation to shrink the database in one of the answers is not advised, as this can lead to fragmentation in the database. If there is any auditing software or anti-virus that is on the new server but not on the old server than this could cause issues. Make sure the new server has recent OS patches applied and compare the SQL Server service pack levels between the two servers.

share|improve this answer
add comment

Your Answer

 
discard

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