In addition to determining the length of the recovery time, the recovery interval in minutes configuration parameter determines how frequently Adaptive Server checkpoints a database. In-memory databases are never checkpointed, but Adaptive Server does checkpoint relaxed durability databases, flushing all modified buffers from disk according to recovery interval in minutes.
Use recovery interval in minutes to reduce the pressure on buffer washing, and to maintain replaceable buffers.The lower the value for recovery interval in minutes, the more frequently Adaptive Server performs a checkpoint and washes all changed buffers. However, you must balance the benefits of performing the buffer wash with the number of disk I/Os that occur while Adaptive Server is performing the checkpoint.
When you set recovery interval in minutes to higher values, you must balance the benefit of a smaller number of disk I/Os that result from less frequent checkpoints with the possibility that a request for a buffer may find it “dirty,” delaying its use while the buffer wash takes place.
recovery interval in minutes applies to all databases server-wide, and you should change this parameter only after evaluating the impact this change has on full-durability databases. If you have at least one full-durability database that generally needs significant recovery after a server crash, continue to use the value required for a timely recovery of this database (generally, the default value of 5 minutes). If a change has little impact on full-durability databases (that is, they require little recovery after a server failure), start with a recovery interval higher than the default value of 5 minutes (for example, 30). After the change, view the Buffers Grabbed Dirty value or the per-cache information in the Data Cache Management sections of sp_sysmon. If the Buffer Grabbed Dirty value is high, decrease the value for recovery interval in minutes.
To decrease the number of I/Os to disk checkpoint performs for relaxed-durability databases without affecting the behavior of full-durability databases (regardless of the value for recovery interval), set the no chkpt on recovery database option to true for the relaxed-durability database. Use the method described above to evaluate the value for Buffers Grabbed Dirty and verify that disabling checkpoints does not negatively impact the availability of reusable buffers.
