x

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

samin gravatar image

samin
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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

+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

SirSQL gravatar image

SirSQL
4.8k 1 3

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x249
x101
x6

asked: Jul 19, 2012 at 06:58 AM

Seen: 1872 times

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