Tuning the recovery interval

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

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

Table 10-3: 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 will write dirty pages to the disk more often. Any checkpoint I/O “spikes” will be smaller.

Recovery period will be very short.

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 the System Administration Guide for information on setting the recovery interval. sp_sysmon reports the number and duration of checkpoints.

See “Recovery management” on page 99 in the book Performance and Tuning: Monitoring and Analyzing for Performance.