Optimizing Server Performance Using I/O Configuration Options
The recovery interval server configuration option can be used to configure I/O usage and improve server performance. The recovery interval server configuration option controls when Microsoft SQL Server issues a checkpoint in each database. By default, SQL Server determines the best time to perform checkpoint operations. However, to determine if this is the appropriate setting, monitor disk write activity on the database files using Performance Monitor. Spikes of activity that cause disk utilization to reach 100 percent can affect performance. Changing this parameter to cause the checkpoint process to occur less often can improve overall performance in this situation. However, continue to monitor performance to determine if the new value has had a positive effect on performance. For more information, see recovery interval Option.