How SQL Server improves the speed of database backups

For a full database backup, the speed of the backup depends largely on the following two factors: Read disk data, log file throughput, write disk data file throughput.

Is the change of disk in the backup process:

Read throughput

The size of the read throughput depends on the speed at which the disk reads data, and the speed of disk reads depends on the location of the data file on the disk. As a result, different database files on different drive characters read at varying speeds.

One way to measure read throughput is to make a full database backup, and then use Windows Performance Monitor (Perfmon) to monitor the read Bytes/sec performance counter for the disk on which the database file resides. The disk where the backup file is saved should be physically different from the disk on which the database file resides, otherwise the measurement accuracy will be inaccurate. Of course the backup should also have some additional read operations from the system or other applications to the disk.

Note: If you use a full backup to monitor disk read and write throughput, the backup file for this test should be put together with other regular backups for recovery purposes. That is, if you perform a regular differential backup after testing the backup file, these differential backups will be backed up as the starting point for the restore with this test.

Assuming that the size of all the database files is equal, the smallest measure you get is the maximum backup throughput you specify for the database in the system.

Another way to measure read throughput is to perform a backup on the NUL device, as follows:

BACKUP DATABASE AdventureWorks to DISK = ' NUL ' with copy_only

Note that we use the COPY_ONLY option, which is only available in SQL Server 2005 and later. You can perform the same backup on SQL Server2000, just ignore this option, but be careful. Because backing up to a NUL device is also considered a valid backup, it means that when you perform a backup to the NUL device, all subsequent differential backups will not be available unless you perform a regular database full backup after performing a backup to the NUL device. If you perform a transaction log backup to the NUL device, you will break the log recovery chain, causing subsequent transaction log backups to be unavailable.

If you have to perform a backup to the NUL device on SQL Server 2000, you must be prepared for disaster recovery.

Suppose I have now measured my AdventureWorks read throughput to 46mb/sec. This means that 46MB/SEC is the largest backup throughput, and it is the fastest speed that my disk can provide to SQL Server backup read threads. So how do we improve this speed? Using a faster disk is definitely a method. Another way is to spread the database files across multiple physical disks so that multiple read threads can be created synchronously while reading the data. Reducing the fragmentation level of a database file can also improve throughput, especially if there is a large amount of fragmentation in the database file.

Write throughput

Now start talking about write throughput. To perform a file backup in my system, I get the following results:

BACKUP DATABASE successfully processed 7529 pages in 3.300 seconds (18.688 mb/sec).

The above results indicate that the write throughput has become a bottleneck here. My disk can provide 46mb/sec data, but the write speed is only 18.688mb/sec. In fact, I put the backup file on the same disk as the data file, and when I put the backup file on a different physical disk, I get the following result:

BACKUP DATABASE successfully processed 7529 pages in 1.421 seconds (43.399 mb/sec).

The results above have been much better. Now the read and write speed depends on the disk, the overall throughput has been significantly improved. So putting the backup file on a different physical disk is a way to improve the write throughput. Another way to do this is to scatter the backups into different files. If the disk can control it, the file can reside on the same physical disk. If not, you'd better spread the files across different physical disks. Using a faster disk storage backup file is another good option.

However, let's go back to the first step and look at the whole picture. Think of the first step to backup throughput is read throughput. This means that even if your write throughput reaches 150mb/sec, it doesn't help if the read throughput is only 46mb/sec, and the maximum backup throughput you can get is 46mb/sec.

Summarize

First we summarize what we have done:

We measured the read throughput as 46MB/SEC, and we discussed the following methods to increase this value:

• Use a faster disk.
• Store multiple database files on a different physical disk
• Reduce database file fragmentation levels

We performed a backup execution on the disk where the database file resides, and the backup throughput is 18mb/sec. At a very bad speed, we know that the read throughput is 46mb/sec, so we put the target on write throughput. We then put the backup file on a different physical disk than the database file. The backup throughput is 43mb/sec. The speed is good. Can I also increase this value? It looks like it's a goner. But if our write throughput is only 25mb/sec, we can also consider the following:

• Use faster disks for backup
• Splitting a backup file into multiple files (on the same or different physical disks, depending on the throughput of the disk)
• Use the Backup compression tool. If the compression speed is very good, then it will reduce the amount of data written to disk, thus increasing the write throughput. In general, executing this compression program consumes a lot of CPU resources

Additional Information

To get the best backup throughput, the following points should be taken into account when you first create the database. In fact, the following points also apply to improve the performance of your database application.

• Disk speed: Improve backup throughput by using the fastest disk or disk configuration with the budget allowed.
• Database files: Spread the database files across multiple physical disks so that SQL Server uses multiple read threads to read data on each disk. In contrast to the database storage of single data files, multi-data files can be read in a very short period of time.
• Use a different physical disk: The number of read threads for SQL Server is based on the number of drive characters that your database file resides in. However, if your drive letter is a partition on the same physical disk, and your disk does not meet the read thread's read requirements, your backup throughput will be poor.
• File fragmentation: The initial size of the database specified when the data is created is equal to the expected maximum value of the specified database to reduce file fragmentation. This also helps to reduce fragmentation if the database file is set to autogrow and a maximum growth value is set.
• Plan to store your transaction log on a separate disk: storing your transaction log files in a separate disk from the database files, or even independent of the operating system or other frequently used I/O applications, helps improve read and write throughput when performing transaction log backups. The I/O operations on the disk of the transaction log are naturally connected, not random operations such as data file I/O. If you put the transaction log file on the same disk as the data file, the transaction log backup slows down when the database is busy.
• Plan to store your backups on separate disks: storing your backup files on a disk separate from the database files, even independent of the operating system or other applications that frequently use I/O, can help improve write throughput.

Get Backup speed data

You can get from msdb. Gets the backup speed data in the Backupset table. The Backup_start_date,backup_finish_date and Backup_size columns provide all the data details needed to calculate the backup speed. Note The backup size is not required to define the database size because SQL Server 2005 does not back up data pages that include data that has been deleted. Please refer to article for specific details.

The following script can show the backup time for all your databases:

SELECT database_name, backup_start_date, CAST (CAST ((Backup_size/(DATEDIFF (SS, Backup_start_date, Backup_finish_date ))/(1024x768 * 1024x768) as NUMERIC (8, 3)) as VARCHAR (+) + ' mb/sec ' speed
From msdb: Backupset
ORDER by database_name, backup_start_date

How SQL Server improves the speed of database backups