Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 73

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 73 Chart 16 - Load Database: Page Reinitialization Performance based on Disk Array Type From the above results it is at once apparent that the type of data disk array does indeed have a huge impact on both phases of the database load. The 8 tape drive array employed had a data transfer rate of about 30 GB/hr; faster than most of the numbers seen in Chart-15. Thus, in all cases except for the RAID-0 tests, performance of the restore phase was limited by the data array, and generally improved when spindles were added to the array. Performance of the RAID-1 array did not improve much beyond 10 drives (~ 25GB/hr) because at this point the Smart-2 SCSI controller became saturated90. The performance of the RAID-5 array during the restore phase is especially bad. This is because the I/O profile to the array during this process consists entirely of random writes, and forces additional I/O's for parity. The I/O is random because 8 tape drives are being read almost simultaneously by 8 different threads, and although the data stream coming from each of the tape drives is sequential, the combination of all the threads writing out the data becomes randomized. The load performance to a slow data array from multiple DLT drives can also suffer if the tape drives are not kept streaming91. Restore performance with SQL Server, especially to a RAID-5 array, is therefore one area where using more tape drives yields less benefit than would restoring from a single high speed device. Restore from a single 35/70-GB DLT to a 12 spindle RAID-5 array proceeded at the speed of the drive (about 19 GB/hr), faster than with 8 15/30-GB DLT's (11.5 GB/hr). Interestingly, using the ARCserve Backup Agent for SQL Server with 8 DLT drives yielded significantly better restore performance to the RAID-5 array: 16.6 GB/hr with 14 spindles (versus 11.9 GB/hr with SQL Server only). This is because the data is read off the 8 tape drives by ARCserve and passed to SQL Server, which then uses less threads to write the data to the disks yielding a less random write profile. Restore performance to the 14 spindle RAID-0 and RAID-1 arrays was not affected by using ARCserve. ARCserve results are not displayed in the above charts. 90 Drive mirroring actually writes twice the amount of data to its disks. So at a 25 GB/hr throughput, the controller was really writing 50 GB/hr of data, or about 7 MB/sec to each of its SCSI ports. 91 As when writing out data during a backup operation, tape drives during a restore perform best when data requests are coming in continuously, so that they do not have to stop, re-position the tape, then continue reading. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797