Microsoft® SQL Server™ offers several methods for increasing the speed of backup and restore operations:
Creating a database backup comprises two steps:
Creating a differential database backup comprises the same two steps as creating a database backup, except only the data that has changed is copied (although all database pages need to be read to determine this).
Backing up a database file comprises a single step: copying the data from the database file to the backup devices.
The database files used to store the database are sorted by a disk device, and a reader thread is assigned to each device. The reader thread reads the data from the database files. A writer thread is assigned to each backup device. The writer thread writes data to the backup device. Parallel read operations can be increased by spreading the database files among more logical drives. Similarly, parallel write operations can be increased by using more backup devices.
Generally, the bottleneck will be either the database files or the backup devices. If the total read throughput is greater than the total backup device throughput, then the bottleneck is on the backup device side. Adding more backup devices (and SCSI controllers, as necessary) can improve performance. However, if the total backup throughput is greater than the total read throughput, then increase the read throughput by adding, for example, more database files on devices or by using more disks in the RAID (redundant array of independent disks) device.
Creating a transaction log backup comprises only a single step: copying the portion of the log not yet backed up to the backup devices. Even though there may be multiple transaction log files, the transaction log is logically one stream read sequentially by one thread.
A reader/writer thread is assigned to each backup device. Higher performance is achieved by adding more backup devices.
The bottleneck can be either the disk device containing the transaction log files or the backup device, depending on their relative speed and the number of backup devices used. Adding more backup devices will scale linearly until the capacity of the disk device containing the transaction log files is reached, whereupon no further gains are possible without increasing the speed of the disk devices containing the transaction log, for example, by using disk striping.
Restoring a database or differential database backup comprises four steps:
Applying a transaction log backup comprises two steps:
Restoring a database file comprises two steps:
If the database and transaction log files do not already exist, they must be created before data can be restored to them. The database and transaction log files are created and the file contents initialized to zero. Separate worker threads create and initialize the files in parallel. The database and transaction log files are sorted by disk device, and a separate worker thread is assigned to each disk device. Because creating files and initializing them requires very high throughput, spreading the files evenly across the available logical drives yields the highest performance.
Copying the data and transaction log from the backup devices to the database and transaction log files is performed by reader/writer threads; one thread is assigned to each backup device. Performance is limited by either the ability of the backup devices to deliver the data or the ability of the database and transaction log files to accept the data. Therefore, performance increases linearly with the number of backup devices added, until the ability of the database or transaction log files to accept the data is reached.
The performance of rolling forward a transaction log is fixed and cannot be further optimized apart from using a faster computer.
There are four variables that affect tape backup device performance and that allow SQL Server backup and restore performance operations to roughly scale linearly as more tape devices are added:
The software data block size is computed for optimal performance by SQL Server and should not be altered.
Many high-speed tape drives perform better if they have a dedicated SCSI bus for each tape drive used. Drives whose native transfer rate exceeds 50 percent of the SCSI bus speed must be on a dedicated SCSI bus.
For more information about settings that affect tape drive performance, see your tape drive vendor’s documentation.
Important Never place a tape drive on the same SCSI bus as disks or a CD-ROM drive. The error-handling actions for these devices are mutually incompatible.
Raw I/O speed of the disk backup device affects disk backup device performance and allows SQL Server backup and restore performance operations to roughly scale linearly as multiple disk devices are added.
The use of RAID (redundant array of independent disks) for a disk backup device needs to be carefully considered. For example, RAID 5 has low write performance, approximately the same speed as for a single disk (due to having to maintain parity information). Additionally, the raw speed of appending data to a file is significantly slower than the raw device write speed.
If the backup device is heavily striped, such that the maximum write speed to the backup device greatly exceeds the speed at which it can append data to a file, then it can be appropriate to place several logical backup devices on the same stripe set. In other words, backup performance can be increased by placing several backup media families on the same logical drive. However, an empirical approach is required to determine if this is a gain or a loss for each environment. Usually, it is better to place each backup device on a separate disk device.
Generally, on a SCSI bus, only a few disks can be operated at maximum speed, although Ultra-wide and Ultra-2 buses can handle more. However, careful configuration of the hardware is likely to be needed to obtain optimal performance.
For more information about settings that affect disk performance, see your disk vendor’s documentation.
Modern tape drives have built-in hardware data compression that can significantly increase the effective transfer rate of data to the drive. Data compression increases the effective transfer rate to the tape drives over what can be achieved with hardware compression disabled. The compressibility of the real data in the database depends both on the data itself and on the tape drives used. Typical data compression ratios range from 1.2:1 to 2:1 for a wide range of databases. This compression ratio is typical of data in a wide variety of business applications, although some databases can have higher or lower compression ratios. For example, a database consisting largely of images that are already compressed will not be compressed further by the tape drives. For more information about data compression, see your tape-drive vendor’s documentation.
By default, SQL Server supports hardware compression, although this can be disabled by using the 3205 trace flag. Disabling hardware compression can, in rare circumstances, improve backup performance. For example, if the data is already fully compressed, disabling hardware compression prevents the tape device from wasting time trying to compress the data further.
For more information about trace flags, see Trace Flags.
Creating a database backup captures only the portion of the database containing real data; unused space is not backed up. The result is faster backup operations.
Although SQL Server version 7.0 databases can be configured to grow automatically as needed, you can continue to reserve space within the database to guarantee that this space is available. Reserving space within the database does not adversely affect backup throughput or the overall time needed to back up the database.
| Minimizing Backup and Recovery Times in Mission-Critical Environments | SQL Server: Backup Device Object |
| SQL Server: Databases Object |