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]