The following server configuration options can be used to configure I/O usage and affect server performance:
The max async IO server configuration option can be used to configure the maximum number of outstanding asynchronous disk I/O requests that the computer can issue against a file. Generally, leave the max async IO server configuration option at the default value of 32. For very sophisticated I/O subsystems with many disks and controllers, the value can be increased to 64 or possibly higher. Monitor disk write activity using Windows NT Performance Monitor, and watch for spikes caused by the lazy writer. If spikes occur that cause the LogicalDisk: %Disk Time counter to peak, then lower the value of this configuration option. If your I/O activity is low, consider raising this value. For more information, see max async IO Option.
The recovery interval server configuration option controls when 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 Windows NT 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.