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.

5 Answers5

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.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
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.

BateTech
  • 766
  • 1
  • 5
  • 13
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 :

AndyB
  • 1
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

digitalohm
  • 26
  • 3