Tuning the recovery interval

The default recovery interval in Adaptive Server is five minutes per database. Changing the recovery interval affects performance because it impacts the number of times Adaptive Server writes pages to disk.

Table 5-2 shows the effects of changing the recovery interval from its current setting on your system.

Table 5-2: Effects of recovery interval on performance and recovery time

Setting

Effects on performance

Effects on recovery

Lower

May cause more reads and writes and may lower throughput. Adaptive Server writes dirty pages to the disk more often. Any checkpoint I/O spikes will be smaller.

Setting the recovery interval lower expedites recovery if there are no long-running open transactions that Adaptive Server must roll back.

If there are long-running open transactions, more frequent checkpoints could slow the recovery process because the disks contains more modifications that Adaptive Server must roll back.

Higher

Minimizes writes and improves system throughput. Checkpoint I/O spikes will be higher.

Automatic recovery may take more time on start-up. Adaptive Server may have to reapply a large number of transaction log records to the data pages.

See Chapter 11, “Developing a Backup and Recovery Plan,” in System Administration Guide: Volume 2 for information on setting the recovery interval. sp_sysmon reports the number and duration of checkpoints. See Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.