Performance Tuning Tips for SQL Server Backup and Restore

You can speed all backups, including disk backups, by backing up to multiple backup devices at the same time. SQL Server creates a separate backup thread for each backup device, allowing backups to be done in parallel to multiple backup devices. This feature is very important for very large databases (VLDB), although this technique can be used with databases of any size. To take full advantage of threaded backups, the disk subsystem must be able to keep up, otherwise the slow performance of a disk subsystem can negate the benefits of using multiple backup threads. [2000, 2005, 2008] Updated 2-3-2009

*****

Perform backups during times of the day with slower activity. This will help prevent I/O bottlenecks and help prevent your backups from affecting user’s response times. [2000, 2005, 2008] Updated 2-3-2009

*****

For large databases that need regular full backups, consider a fiber-based SAN (storage area network) storage and backup solution for the ultimate in performance. [2000, 2005, 2008] Updated 2-3-2009

*****

If you suspect that your backup or restore operations to disk are running at sub-optimal speeds, you can help verify this by using one or more of the following Performance Monitor counters to measure I/O activity during a backup or restore:

SQL Server Backup Device Object: Device Throughput Bytes/sec: This counter measures how much data is being backed up or restored. While there is no absolute value this counter should show, it should give you an idea of how fast your backups or restores are occurring. If this value appears to be small in relation to how fast you think your I/O system is, then perhaps there is some bottleneck preventing your backups or restores from occurring faster.

Physical Disk: % Disk Time: As a rule of thumb, the % Disk Time counter should run less than 55%. If this counter exceeds 90% for continuous periods when performing backups or restores (over 10 minutes or so) then your SQL Server may be experiencing an I/O bottleneck. If you suspect a physical disk bottleneck, you may also want to monitor the % Disk Read Time counter and the % Disk Write Time counter in order to help determine if the I/O bottleneck is being mostly caused by reads or writes.

Physical Disk Object: Avg. Disk Queue Length: If the Avg. Disk Queue Length exceeds 2 for continuous periods when performing backups or restores (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. You will need to calculate this figure because Performance Monitor does not know how many physical drives are in arrays.

If you find that you do have an I/O bottleneck during backups or restores, your options to correct this include increasing the speed of your disk I/O system, reducing the load on your current system by performing backups or restores on less busy times, or backing up to a local tape device or over the network (assuming you are not doing that now). [2000, 2005, 2008] Updated 2-3-2009

*****

If you backup your SQL Server databases directly to a local tape drive, the local type drive should be connected to its own dedicated SCSI or fiber connection, not a connection shared with drive arrays. This produces maximum backup performance and prevents conflicts with drive array access. [2000, 2005, 2008] Updated 2-3-2009

*****

If you are backing up directly to tape, and performance is not adequate, the first step is to determine where the bottleneck is. In other words, is the bottleneck due to the tape drive, or to other factors?

To help make this determination, you need to know what the rated throughput of your tape device is. For example, if your tape backup device is rated at 10GB per hour, and it takes two hours to backup 20GB, and you feel that this is taking too long, then most likely the tape device is the bottleneck, and your only option to speed your backups is to get a faster backup device.

But if your tape backup device is rated at 10GB per hour, and it takes 2 hours to backup 10GB, then most likely there is some other bottleneck preventing the tape backup from working to its full capacity. [2000, 2005, 2008] Updated 2-3-2009

*****

If you are backing up directly to tape, consider using parallel tape drives. Doing so allows SQL Server to backup using multiple threads (one for each tape drive), and backups get completed much faster[2000, 2005, 2008] Updated 2-3-2009

Continues…

Leave a comment

Your email address will not be published.