Database Backups & Restores: an in-depth knowledge required

I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff
What are the server manufacturer and model numbers? What are the FSB
speeds? What are the I/O controller speeds? What is the System:Worker
Queue counter throughput? What are the Physical/Logical disk counter
throughput?
Sincerely,
Anthony Thomas

"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
I have two servers.
Server A:
Dual 2.8 Xeon Processor
4 GB RAM
SQL Server 2000 Enterprise
Server B
Single 3.0 Xeon Processor
2.5 GB RAM
SQL Server 2000 Standard
I have an identical database on both. The backup file is 1.22 GB, the data
file is 1.44 GB and the log file is 504 KB.
When I restore these databases, the restore on server A takes 2.58 minutes,
but on server B it takes 6.48 minutes (over twice as long).
Similar discrepancies are found when I backup these databases.
I don't understand why!
Studying task manager shows that the CPU usage is minimal and the amount of
memory used is minimal, presumably ruling out the speed of the processors
and the amount of RAM available. If I had multiple data devices then I
could see the Enterprise version being quicker because it could create more
threads for parallel processing, but I don't so this should rule out the
Enterprise/Standard differences. Oh and both servers run on multiple
spindle, fast RAID5+1 disks, and these are identical.
So...what is occurring that actually takes the ~ 3 minutes or ~ 7 minutes?
Many thanks
Griff
|||
> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff
|||Well perhaps it's your specs
btw u can run CPUZ to identify bus speeds etc.
If the machines have same quality of equipment and are configured similarly
then I would suggest your ram and dual CPU vs single CPU
If you run the profiler on them as they are backing perhaps you will see
something like mem utilisation etc.
Another thought is how much are the files fragmented the dbs arent huge but
there may be a degree of fragmentation on the file system which slows things
down.
Also how are you restoring from Server A to Server B?
"Griff" <Howling@.The.Moon> wrote in message
news:%23wUo43l8EHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have two servers.
> Server A:
> Dual 2.8 Xeon Processor
> 4 GB RAM
> SQL Server 2000 Enterprise
> Server B
> Single 3.0 Xeon Processor
> 2.5 GB RAM
> SQL Server 2000 Standard
> I have an identical database on both. The backup file is 1.22 GB, the
data
> file is 1.44 GB and the log file is 504 KB.
> When I restore these databases, the restore on server A takes 2.58
minutes,
> but on server B it takes 6.48 minutes (over twice as long).
> Similar discrepancies are found when I backup these databases.
> I don't understand why!
> Studying task manager shows that the CPU usage is minimal and the amount
of
> memory used is minimal, presumably ruling out the speed of the processors
> and the amount of RAM available. If I had multiple data devices then I
> could see the Enterprise version being quicker because it could create
more
> threads for parallel processing, but I don't so this should rule out the
> Enterprise/Standard differences. Oh and both servers run on multiple
> spindle, fast RAID5+1 disks, and these are identical.
> So...what is occurring that actually takes the ~ 3 minutes or ~ 7
minutes?
> Many thanks
> Griff
>
>
|||Well, you asked for possibilities, these are only a few, and they were
static, physical configuration differences. Then you have the runtime
environment differences.
I guess my point is that you are trying to compare apples to oranges. If
you run the backups several times in a row, over and over, on both boxes,
and then average out the times, do you then still see the same magnitude of
difference?
I would be concerned if you were running two identical servers with the
identical work load, but, in this case, you are running under very different
circumstances and it is these differences that will add up to what you are
experiencing.
Howerver, I suspect that the larger server has more robust subsystem
components. CPU speed is only one criterion you look at one analyzing a
server's performance. Moreover, what is the memory bus speed and flavor of
memory? DDR at 2800 or higher vs. only a PC 233. Obviously, they are
probably very similar, but I hope you get my point.
Sincerely,
Anthony Thomas

"Griff" <Howling@.The.Moon> wrote in message
news:O$1a2Xm8EHA.3236@.TK2MSFTNGP15.phx.gbl...

> What are the server manufacturer and model numbers? What are the FSB
> speeds? What are the I/O controller speeds? What is the System:Worker
> Queue counter throughput? What are the Physical/Logical disk counter
> throughput?
Gosh !
Not sure I can provide these answers. I know that they're both using RAID
5+1 10,000 RMP disks with independent raid controllers. The only
significant difference between the two could be the FSB (one is 800, other
is likely to be 533, but not sure how to identify this).
Whilst I could spend time providing these statistics, I may be more
profitable if I could identify what the real bottle-necks really are (if all
the ones you've mentioned, then in which order) and would they account for a
doubling of time?
Thanks
Griff
|||Got the latest version of CPU-Z
The slow machine...it won't report the BUS speeds (in fact, it reports very
little).
As for how I restore...I just use Enterprise Manager and do it from there.
Griff
|||Griff,
What I was referring to was where is the backup device you're restoring
from?
If one is local (ie on disk) and the other is over the network then you will
see slower restores.
TBH I suspect the single CPU job is not upto the same performance and you
are realising this in what you're doing.
evan
"Griff" <Howling@.The.Moon> wrote in message
news:edUZHjn8EHA.3640@.tk2msftngp13.phx.gbl...
> Got the latest version of CPU-Z
> The slow machine...it won't report the BUS speeds (in fact, it reports
very
> little).
> As for how I restore...I just use Enterprise Manager and do it from there.
> Griff
>
|||> What I was referring to was where is the backup device you're restoring
> from?
> If one is local (ie on disk) and the other is over the network then you
will
> see slower restores.
Oh, sorry. Everything is local...I tried to make the two tests as
identical as possible

> TBH I suspect the single CPU job is not upto the same performance and you
> are realising this in what you're doing.
Okay. Thanks for your help with this Evan
Griff