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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 68 common transaction log volumes (fault tolerance and number of drives) that may be implemented on an array controller, along with the approximate throughput they can be expected to deliver when performing read operations during a SQL Server dump: Table 7 - Max Dump Throughput for Small Arrays RAID Level Spindles Throughput 1 2 12.5 GB/hr 1 4 20 GB/hr 1 6 25 GB/hr 5 3 25 GB/hr 5 4 30 GB/hr When dumping the transaction log by itself, the need to also truncate the committed transactions seems to limit the operation to a maximum throughput of about 15 GB/hr. When dumping the transaction log by itself and avoiding truncates, the small, single-page reads used limit the operation to about 12 GB/hr. Increasing the number of spindles in the log volume does not yield increases in these numbers. The rate at which a transaction log dump can write data to a single tape drive can also differ from the rate seen for the database dump, depending on the amount of compression the drive is able to achieve on the transaction log vs. compression on the data. Transaction logs generally lend themselves well to compression, although this can depend on the nature of the transactions recorded in the log (e.g.: transactions which repeatedly modify the same or similar rows will yield a higher compression rate on the log). In our environment, a transaction log dump to one DLT drive yielded throughput higher than for a database dump (6.5 GB/hr vs. 5.0 GB/hr). Unexpectedly however, this number did not increase and in fact went down - when a striped dump was done to multiple DLT drives! The SQL Server Dump Transaction process does not seem to be able to keep the tape drives streaming for a striped operation. The results were similarly low when selecting the option to avoid log truncates. The reason for this is because the DUMP TRAN process uses only a single thread to write to all of the output devices, even when doing a striped operation. When using an ARCserve agent to perform the transaction log dump to multiple drives however, this performance limitation was bypassed; as ARCserve uses its own, multiple threads to write out the data. The below table summarizes some of the results encountered with transaction log dumps: Table 8 - Max Transaction Log Dump Throughput with Various Commands Command w/ DUMP DATABASE Software SQL Server To Device(s) NULL Throughput Max87 SQL Server 1 15/30 DLT 7.2 GB/hr SQL Server 1 35/70 DLT 20 GB/hr SQL Server 8 15/30 DLT's 29 GB/hr ARCserve 8 15/30 DLT's 34 GB/hr 87 Throughput depends on disk configuration used to hold transaction log volume.