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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 20 out of space. Next, disable compression on the drive and repeat the operation. Divide the two numbers to obtain a compression ratio or percentage estimate. When data is written to the tape media, it is formatted for a certain blocking factor. The number of bytes per block is usually determined by the application when sending the data to the tape driver. When working with DLT Tape drives, this block size is an important consideration, as it can have serious performance implications. Ideally, the DLT drive can benefit from as large a block size as possible and the minimum block size recommended is 8KB. However, many applications do not use the larger block sizes due to in-memory buffering constraints, and some applications unfamiliar with DLT technology even use block sizes smaller than 8KB, because most other kinds of tape drives perform well with block sizes that can be much smaller (512 bytes or 1KB). A well designed application will first query the tape driver with the appropriate API call to determine the kind of tape drive before attempting to set the block size. SQL Server 6.0 does exactly that, and sets the drive's block size to 8KB if a DLT drive is detected.24 Actual SCSI data transfers are done to/from the DLT drive in 64KB sizes across the SCSI bus, but are further formatted for an 8KB blocking factor by the DLT before writing them to the tape media. The drive is set for fixed block mode, in which it sends or receives a fixed amount of 'blocks' in each data transfer, in this case 8 blocks per transfer. Even with an 8KB block size however, limitations will still be encountered. These limitations are most apparent when using compressible data sets. The overhead involved in formatting the data for an 8KB block size establishes a ceiling on how fast the DLT drive can write the data onto the tape. Thus the additional advantage gained by having the drive compress the data is not realized here. When an increase is made to a larger block however, the drive is able to write the data much faster so that the advantages of hardware compression become apparent. The chart below contains data acquired by varying the block size used by a certain tape backup application under Windows NT, while also changing the patterns within the data file. For each block size, the bars on the left and right represent throughput achieved with data sets containing completely random patterns (should ideally yield 0% compression) and half random patterns (should ideally yield 50% compression), while the bar in the middle represents compression disabled on the drive. As can be seen, at 8KB it will generally make little difference what the compressablility of your data sets is, the results will not vary greatly beyond the standard rate of 4.5 GB/hr specified for the 10/20-GB and 15/30-GB DLT drives (this is the rate seen when compression is completely disabled on the drive). At 64KB however, the drive yields throughput more in line with the compression ratios of the data: with a 50% random data set we see an almost 2:1 gain in throughput. Also, note that when using a highly uncompressable data set, the throughput is slightly lower than when disabling the compression altogether, due to the overhead involved in having the compression logic process the data. 24 SQL Server versions prior to 6.0 would use a hard-coded block size of 512 bytes, which had serious performance repercussions in the case of DLT tape drives. As a result, Compaq recommends that DLT drives not be used with these earlier (i.e: version 4.21x) SQL Servers. 512 byte blocks work fine for DAT and QIC drives.