May 30, 2009 at 1:54 pm
Hi,
We have database of 45 GB. I resotred this database in Server A and Server B.
In Server A, it is taking 20 mins. But In Server B, it is taking 1:40 mins. Both Servers have 4 GB of RAM.
I did not understand why this much big difference in restore timings.
Could you please tell me how can I diagnose this?
May 30, 2009 at 1:58 pm
Are you restoring from a file on the network? Or is the file local? The I/O system is usually what causes restore rates to be fast or slow - so I would check that also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2009 at 3:15 pm
Is anything different running on the servers? Where is the backup file v the data files on each server? As Jeffrey mentioned, I/O is usually to blame here.
May 30, 2009 at 7:15 pm
thank you,
Iam restoring from the local drive only.
In Server A (Development Server), is taking 20 mins, which is a Virtual server having Backfile and datafiles on same drive
Server B, (Production Server) is taking 1:40 mins, which is Physical machine and having Backfiles on Z drive, datafiles on D drive and Log files on E drive.
Where can Check this IO?
May 30, 2009 at 10:33 pm
Well , the duration of the IO depends upon different hardwarw : amount of RAM , spindles and potential of H-Disks , Device drivers , OS and the patch level , different hardware settings , CPU(s)....
Most probably one of the server is either busier in doing other activities (thus consuming resources already) or need more powerful hardware equivalent to the server where your virtual server is operational (i myself will not bet on this point)..
Check these values in perfmon and compare :
Memory
Available Mbytes
> 100MB
Paging File
%Usage
< 70%
Process (sqlservr)
%Privileged Time
< 30% of %Processor Time (sqlservr)
Processor
%Privileged Time
< 30% of Total %Processor Time
PhysicalDisk
Avg. Disk Sec/Read
< 5ms
PhysicalDisk
Avg. Disk sec/Write
< 5ms (non cached) < 1ms (cached)
PhysicalDisk
Avg. Disk queue length (2 per spindle can be tolerated)
PhysicalDisk
Avg. Disk write queue length (2 per spindle can be tolerated)
if you are taking native backup then check the errorlog files for information : IO reqest taking more than 15 seconds ...
Perfmon will be able to give you the overall system ststus on both the machines and then you can compare ..
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 1, 2009 at 8:04 am
rambilla4 (5/30/2009)
thank you,Iam restoring from the local drive only.
In Server A (Development Server), is taking 20 mins, which is a Virtual server having Backfile and datafiles on same drive
Server B, (Production Server) is taking 1:40 mins, which is Physical machine and having Backfiles on Z drive, datafiles on D drive and Log files on E drive.
Where can Check this IO?
I would guess that "Z" is a networked drive, and you are being limited by your network throughput.
Student of SQL and Golf, Master of Neither
June 1, 2009 at 8:19 am
Not sure that z: is a network drive. We standardized on z: for our servers years ago as the data drive for local storage. That way we knew it was Z: for data always, whether you had 1, 2, or 0 CD drives.
As mentioned, there are a lot of things to check to see why things are taking long.
June 1, 2009 at 8:31 am
rambilla4 (5/30/2009)
thank you,Iam restoring from the local drive only.
In Server A (Development Server), is taking 20 mins, which is a Virtual server having Backfile and datafiles on same drive
Server B, (Production Server) is taking 1:40 mins, which is Physical machine and having Backfiles on Z drive, datafiles on D drive and Log files on E drive.
Where can Check this IO?
At guess i would be looking at issues with the Virutual server, I have seen a few strange issues crop up when working with a Virtual server compared to a physical server.
June 1, 2009 at 8:50 am
Steve Jones - Editor (6/1/2009)
Not sure that z: is a network drive. We standardized on z: for our servers years ago as the data drive for local storage. That way we knew it was Z: for data always, whether you had 1, 2, or 0 CD drives.
True, but he says data is on D and logs on E, so my best guess from the info he presented was that Z is a networked drive. On the dev server, the backup and the data were on the same drive - also a problem, but for most implementations a networked drive would be much slower.
Student of SQL and Golf, Master of Neither
June 1, 2009 at 4:11 pm
Hi All,
Z is NOT a Network Drive. Its local to the server
June 3, 2009 at 8:26 am
rambilla4 (6/1/2009)
Hi All,Z is NOT a Network Drive. Its local to the server
Can you tell us anything else about it? Is it a USB drive, firewire, or a normal hard drive attached to the internal bus comparable to the other server's drives?
Student of SQL and Golf, Master of Neither
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply