ADSM-L

Re: TDP for SQL Restore Performance

2000-05-17 16:14:59
Subject: Re: TDP for SQL Restore Performance
From: Robert King <bobking AT US.IBM DOT COM>
Date: Wed, 17 May 2000 16:14:59 -0400
Rick, performance is always a tricky area and every situation is different.
But these are some things to consider. Perhaps one will lead you to an
answer.

Network: Are the reported network data transfer speeds pretty much the same
between backup and restore? (If the amount of data transferred is small
these numbers cannot be reliably compared.) Can you independently verify
that the network transfer speeds are the same in both directions (FTP or
copy to/from a mapped drive)?

TSM Server: The restore tape is mounted before timing starts?

TDP for SQL: It is possible for the restore uncompress to take
significantly longer than the backup compress. Do you see a similar
disparity with compression off?

SQL Server:
SQL Server needs to read only the used pages in a database for a backup but
must not only write all used pages for a restore but also initialize all
unused pages. Is the used space in the database much less than the free
space? How do the read and write speeds of your disks compare?

If there are a lot of open transactions when the backup is taken, recovery
may take a long time on restore. Do you notice a lengthy recovery time?

Your RAM is large enough that SQL Server may not need to read from disk for
the backup but it must always write to disk for the restore. Can you do a
SQL Server native backup and restore and see how their times compare?
Comparing the native times to the TDP/TSM times is not an apples to apples
comparison but it may help eliminate some possibilities.

Bob King    IBM Global Services
bobking AT us.ibm DOT com

> ...
> Backup:
>
> I'm backing a database approximately 1GB (uncompressed) in size and the
> backup takes about 2 mins. The backup goes straight to tape. A backup to
> disk takes about 3 mins.
>
> Restore:
>
> The restore of that same database took about 20 mins.
>
> Client:
> HP LXR PRO 8, 2x200 MHz processors, 1GB RAM, Client version 1.1.2.01
>
> Server:
> IBM Network Storage Manager, 2x332 MHz processors (H50), 512 MB RAM,
Server
> version 3.7.1, 3494 tape library with 3590 tape drives.
>
> Network:
> 100 Mb Ethernet dedicated (no other traffic) set to full duplex.
> ...
<Prev in Thread] Current Thread [Next in Thread>