Optimizing Backup and Restore Performance in SQL Server
Microsoft SQL Server offers the following two ways of speeding up backup and restore operations:
Using multiple backup devices allows backups to be written to all devices in parallel. Backup device speed is one potential bottleneck in backup throughput. Using multiple devices can increase throughput in proportion to the number of devices used. Similarly, the backup can be restored from multiple devices in parallel. For more information, see "Using Multiple Media or Devices," later in this topic.
Using a combination of full, differential, and (for the full or bulk-logged recovery model) transaction log backups to minimize to recovery time. Differential database backups are normally faster to create than full database backups and reduce the amount of transaction log required to recover the database. For more information, see Creating Full and Differential Backups of a SQL Server Database.
Using Multiple Media or Devices
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 with the number of backup devices, until the maximum throughput of the database or transaction log files to accept the data is reached.
Using multiple backup devices for backup and restore operations enables SQL Server to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time as other backup devices. For enterprises with large databases, using many backup devices can greatly reduce the time taken for backup and restore operations. SQL Server supports a maximum of 64 backup devices for a single backup operation.
While a backup is being written to multiple backup devices, several internal synchronization points occur. The most important such point occurs when all the data in the database has been backed up and the transaction log is about to be backed up.
Important
When multiple backup devices are used to perform backup operations, the backup media involved can be used only for SQL Server backup operations. For more information, see Using Backup Media.
Creating and restoring backups when you use multiple backup devices is the same as creating and restoring backups when you use a single device. The only difference is that you must specify all backup devices involved in the operation, not just one. For example, if a database backup is to be created that uses three tape backup devices such as \\.\TAPE0, \\.\TAPE1, and \\.\TAPE2, each of the tape devices must be specified as part of the backup operation, although fewer tape backup devices can be used when you restore the backup later.
When you create a backup on multiple backup devices by using removable media, the devices can operate at different speeds and the media volumes can have different amounts of space available. During the backup operation, if the media volume on a backup device runs out of space, the operation stops writing to that device and prompts you for a new media volume. Until you replace the filled media volume with an empty volume, that device is blocked. Meanwhile, the backup operation continues writing data to the devices whose media still have space available. When you replace the filled media volume, its device becomes available and the backup resumes writing data to that device. However, be aware that if an internal synchronization point occurs while any device is blocked, the backup operation pauses entirely until that device is made the available again.
Example
Consider a scenario that uses three tape backup devices of equal speed to store a full database backup. The first two tapes have 10 gigabytes (GB) of available space, but the third has only 5 GB available. If a 20-GB database is backed up to all three tape backup devices simultaneously, the third tape will fill up before the backup completes. After 5 GB of data have been written to the third tape, the backup operation stops writing to the third device. The operation blocks that device and prompts for a new tape. Meanwhile, the backup operation continues writing data to the other two devices. However, before the third tape is replaced, an internal synchronization point occurs. At that point, the whole backup operation pauses until a new tape is mounted on the third device.
Optimizing Performance for Full and Differential Backups
Creating a full or differential backup consists of the following steps:
Copying the data from the database files to the backup devices.
Copying the portion of the transaction log needed to roll forward the database to a state consistent with the same backup devices.
Creating a differential backup is identical to creating a full backup, except only changed data is copied. Backing up a database file consists of simply copying the data from the 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, a 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; for example, by adding more database files or devices (or by adding more disks to a RAID device).
Optimizing Transaction Log Backup Performance
Creating a transaction log backup involves simply 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 writer thread is assigned to each backup device. Higher performance is achieved by adding more backup devices.
A 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, after which no further gains are possible without increasing the speed of the disk devices containing the transaction log, by using disk striping, for example.
Optimizing Restore Performance
Restoring a database or differential backup consists of four steps:
Creating the database and transaction log files if they do not already exist.
Copying the data from the backup devices to the database files.
Copying the transaction log from the transaction log files.
Rolling forward the transaction log, and then restarting recovery if necessary.
Applying a transaction log backup consists of two steps:
Copying data from the backup devices to the transaction log file.
Rolling forward the transaction log.
Restoring a database file consists of two steps:
Creating any missing database files.
Copying the data from the backup devices to the database files.
File Initialization
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 and initializing files requires very high throughput, spreading the files evenly across the available logical drives yields the highest performance.
Instant File Initialization
In SQL Server 2005 and later versions, data files can be initialized instantaneously, allowing for fast execution of database or filegroup restore operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log file initialization still requires zeroing, but it will happen in parallel with the transfer of the data from the backup. The roll forward step of restore will not start until all of the data has been transferred and the whole log has been initialized.
Note
Instant file initialization is available only on Microsoft Windows XP, Windows Server 2003, or later systems.
To use instant file initialization, you must run the MSSQLSERVER service account under a Windows account and assign the Windows SE_MANAGE_VOLUME_NAME special privilege to that Windows account. This privilege is assigned to the Windows Administrators group by default. If you have system administrator rights, you can assign this privilege by adding the Windows account to the Perform Volume Maintenance Tasks security policy. For more information about assigning user rights, see the Windows documentation.
Optimizing Tape Backup Device Performance
Several variables affect tape backup device performance and allow SQL Server backup and restore performance operations to roughly scale linearly as more tape devices are added:
Software data block size.
Number of tape devices that share a small computer system interface (SCSI) bus.
Tape device type.
The software data block size is computed for optimal performance by SQL Server and should not be altered. The maximum BLOCKSIZE is 64 KB.
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 to avoid loss in performance. For more information about settings that affect tape drive performance, see the 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.
When performing multiple backup operations to a loaded tape, you can improve performance by specifying NOREWIND. This option causes SQL Server to keep the tape or tapes open after the backup operation. NOREWIND implies NOUNLOAD.
Optimizing Disk Backup Device Performance
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 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 the disk vendor's documentation.
Data Compression
Modern tape drives have built-in hardware data compression that can significantly increase the effective transfer rate of data to the drive. 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 the tape-drive vendor's documentation.
By default, SQL Server supports hardware compression, although this procedure 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 (Transact-SQL).
Backup Compression
By default, backing up using backup compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).
Amount of Data Transferred to Tape
Creating a data or differential 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 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.
Optimizing Log Shipping Synchronization
When trying to synchronize a log shipping destination, you do not have to use WITH STANDBY between RESTORE LOG steps.