5

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.

3
  • 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 '14 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 '14 at 0:14
  • See if this :dba.stackexchange.com/questions/53726/… helps you.
    – Kin Shah
    Mar 26 '14 at 12:03
3

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).

Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I restore a database to a different server

You can happily use the SQL Server Index and Statistics Maintenance - from Ola Hallengren.

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.

I have a detailed post restore steps outlined in this answer. That will help you. Even though it is for an upgrade, but the post restore steps are same.

The only exception being the new VM has a lot more RAM

If the only exception is more RAM on the new server, then try enabling the Trace Flag T2335. Paul White has a detailed explanation of why that would happen with MORE Memory. I have shared, what Microsoft recommended in our case as well.

3
  • Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I restore a database to a different server--why? Apr 23 '18 at 2:06
  • Comes indirectly from Do you need to update statistics after an upgrade? .. Its part of a post restore step that I do by running Ola's reindexing routine just to rule out - when people come to you saying .. did you update stats or rebuild/reorg indexes as they are seeing slowness after a db refresh.
    – Kin Shah
    Jun 11 '18 at 15:38
  • 1
    I can confirm this answer helped us. Our customer migrated a production database from their prod server running SQL 2008 R2 and restored it to a new test server running SQL 2016 to test a software update. During UA testing they reported dreadful performance running certain queries (15s vs 5m). Turns out they omitted the maintenance plan that normally rebuild indexes and updates statistics. Once they ran the maintenance plan to rebuild indexes and update stats the performance problems on the new instance exceeded the performance on current prod by 3s (12s on SQL 2016 instance). Dec 4 '19 at 15:39
1

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.

0

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.

0

In addition to the above suggestions, have you also duplicated the tempdb configurations? It may be that your previous server had multiple tempdbs configured, while your new one only has one. If you need to configure them, have a look here :

0

I would make sure you update stats with the paranoid option.

USE db_name;
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

Outside of that definitely go back and check what the differences are, and discuss them with your virtualization guys

1
  • Since sp_MSforeachtable has some issues, look at Aaron Bertrand's approach to that sort of operation.
    – RLF
    Aug 28 '14 at 18:41

Your Answer

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