Takes too times for restore database

Few weeks ago, I have shifted my database (MS Sql 2005) to a new high configured windows server. BUT it seems slower than previous one. When I restore database from backup file in local disk , it takes 30 minutes whereas the time was only 10 minutes in my previous sever. My database size is now 3.5 GB. and also sql query is slower then. I am looking for the solutions / advice. Here I mention the hardware configuration below for the bothe servers:

OLD Server: Intel Xeon 2.50 GHz 4GB RAM Windows Server Standard Edition 2003

NEW SERVER: Intel Xeon 2X2.46 GHz (2 Processors) 32 GB RAM 16 core RAID 5

more ▼

asked Jul 19, 2012 at 06:58 AM in Default

avatar image

10 1 1 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Check the server configuration to see if it's been set up with the power saving mode enabled. It's a very common factory setting (for some insane reason) and it will seriously negatively impact performance.

more ▼

answered Jul 19, 2012 at 09:37 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left

There could be an issue with the new server. The best possible way could be to re-store the same backup on the old server and see the difference. Depending upon the server type, these following articles by Brent Ozar may help

How Fast is Your SAN? (Or How Slow?)

Slow Storage Reads or Writes

Another factor to look at could be the number of VLFs. Did you do some activity before migration, which could have increased the Log File considerably. The higher the number of VLFs, the longer it will take to restore.

How a log file structure can affect database recovery time

more ▼

answered Jul 19, 2012 at 07:51 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

Another idea: has the executing sql server account the right to perform instant file initilization? Take a look here: http://msdn.microsoft.com/en-us/library/ms175935%28v=sql.105%29.aspx

That should shorten the restore time a bit.

more ▼

answered Jul 19, 2012 at 08:01 AM

avatar image

1.3k 15 19 24

+1, Log files are not benefited from instant file initialization, log files are always initialized by filling Zeros.

Jul 19, 2012 at 09:51 AM Cyborg
(comments are locked)
10|1200 characters needed characters left

You mention the new server is RAID5 but not what the configuration was on the old. You could run into issues depending upon how your disk is configured. Do you have a single presented lun that has been split into multiple drives? Are your backups being stored on the same disk as the database file? How many spindles on the new versus the old? How about your RAID controller, is it single or multi-channel? Is write caching enabled for the RAID controller?

However these settings are I would be seriously concerned. A 3.5GB database restores very quickly on my laptop, there's no way it should take that long on a server. I'd recommend capturing some perfmon statistics while doing your restore to help identify contention.

more ▼

answered Jul 19, 2012 at 03:39 PM

avatar image

4.9k 4 5

(comments are locked)
10|1200 characters needed characters left

Does your new SQL server have the right RAM settings? You may have too little and are then suffering accordingly.

more ▼

answered Jul 20, 2012 at 09:48 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 19, 2012 at 06:58 AM

Seen: 2824 times

Last Updated: Dec 11, 2012 at 04:55 PM

Copyright 2016 Redgate Software. Privacy Policy