Performance Tuning Tips for SQL Server Backup and Restore

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). [6.5, 7.0, 2000, 2005] Updated 3-20-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

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. [6.5, 7.0, 2000, 2005] Updated 6-27-2006

*****

If your transaction log backups are negatively affecting your users because they take too long to run, consider backing up the logs more often so they are smaller. The smaller they are, then the less impact there will be on the server when the backup occurs. It is not uncommon to perform transaction log backups as often as 5 to 15 minutes, depending on transaction activity and on how much data you are willing to loose, assuming the database becomes corrupt. [6.5, 7.0, 2000] Updated 9-4-2006

*****

SQL Server 2000 and SQL Server 2005 offer three database recovery models: Simple Recovery, Full Recovery, and Bulk-Logged Recovery. The database recovery model you choose can affect its performance during these operations: BULK INSERT, SELECT INTO, and CREATE INDEX (including indexed views), WRITETEXT, and UPDATETEXT.

For example, if you choose the Full Recovery model, BULK INSERT, SELECT INTO, and CREATE INDEX (including indexed views), WRITETEXT, and UPDATETEXT operations are all fully logged. While this ensures the best recoverability, it is not the fastest or most efficient way to perform these operations.

If you want to speed these operations, you can choose the Bulk-Logged Recovery model, where these operations are minimally logged. This option still provides a good level of recoverability, but is faster than using the Full Recovery model. This option is similar to the SELECT INTO/BULK COPY database option available with older versions of SQL Server. The Simple Recovery model is most similar to the TRUNC. LOG ON CHECKPT. option available with older versions of SQL Server. [2000, 2005] Updated 9-4-2006