A backup process can be separated into three distinct operations:

• SQL Server backup engine reading data and log files, incurring disk reads I/O.
• SQL Backup compressing (and possibly encrypting) the data, consuming CPU cycles.
• SQL Backup writing the resulting compressed data to files to disk, incurring disk writes I/O.

To start off, run a backup process using SQL Backup with the NOCOMPRESSWRITE option. This will tell you the maximum possible backup throughput attainable on your system, as SQL Backup will not compress nor write the backup data to disk (i.e. it runs only the first operation). You should also test the effects of utilizing multiple threads if you are using a multi-processor system. Usually, the limiting factor at this point is the speed at which your disks can read the data from the disks.

To see the effects of compression on the backup throughput, run a backup process using the NOWRITE option. This will run only the first and second operations. No backup file will be created. You can then see the effects different compression levels have on the backup speed. To improve the throughput, you would usually need to use multiple threads until you can attain a speed close to that achieved with the NOCOMPRESSWRITE option.

Finally, run a complete backup process, using the number of optimal threads obtained while using the NOWRITE option. To optimize this third operation, you would usually store the backup data on a different set of disks from those used by the data and log files. Depending on the type of disk controllers you have, you may need to reduce the number of backup devices, in order not to overload the disk writes I/O. Your aim is to balance the number of backup devices with the number of disks you can back up to, in order to attain a throughput close to the numbers you obtained when running with the NOWRITE option. Monitoring the Current Disk Queue Length performance counter will tell you when you have reached the maximum capacity of your disks. As a rule of thumb, the number should not exceed twice the number of drives on your disk array. If you can achieve this, you have pretty much optimized your backup process as much as possible using SQL Backup.

Example:

• Ran a backup using the NOCOMPRESSWRITE option. Attained a throughput of 103 MB/sec. Increased the number of threads to two. Throughput is still 103 MB/sec. Thus, the limiting factor is our disk read speed, which tops out at 103 MB/sec.
• Ran the same backup using the NOWRITE option. Attained a throughput of 60 MB/sec. Increased the number of threads to two. Throughput increases to 99 MB/sec. Increased the number of threads to three and throughput tops out at 103 MB/sec. Changed to compression level two. Using one thread, throughput is 80 MB/sec. Using two and three threads, throughput is 103 MB/sec. Since we are optimizing for speed, we will use compression level two with two threads, as there is no point using three or more threads.
• Ran a complete backup process using compression level two and two threads. Throughput is 77 MB/sec. Increased the number of threads to three and throughput drops to 71 MB/sec. Using two threads is the optimal option in this case and for this system. Current Disk Read Queue performance counter averages 13 for a 4-disk array.