Two weeks ago, in "Zen and the Art of Hardware-Resource Distribution" ( http://lists.sqlmag.com/t?ctl=E5F:7B3DB ) I said many people have I/O problems they don't know about. If you think you might be one of those people (or even if you know SQL Server I/O Inside and Out--capitalization pun intended
I can't summarize all of the 28-page paper, but I'll share one interesting point about I/O as it relates to recovery intervals. Some people attempt to tweak the recovery interval to improve I/O efficiency or reduce contention during a checkpoint. Bob writes: "Several specific conditions (side effects) can arise when the recovery interval is adjusted. Weigh these carefully before adjusting the recovery interval." When you increase the checkpoint interval by increasing the recovery interval, you shift the pressure of handling dirty pages (pages on which data modifications have taken place) to the lazy writer code line. Because the lazy writer isn't designed to perform checkpoint-like activities, interval changes degrade performance. "The lazy writer does perform proper activity on the dirty pages to ensure data integrity and free list maintenance," Bob says, "but unlike the checkpoint process, it isn't designed to remove the dirty page I/O latency." He goes on to say that "Checkpoints allow dirty pages to be written more aggressively." If you leave the checkpointing actions to the lazy writer, you introduce latency because the lazy writer is forced to perform I/O to age a buffer.
I doubt I'm the only person who's experimented with changing the recovery interval to increase the time between checkpoints. Sometimes we like to think we're smarter than SQL Server and tweak defaults for better performance--which is sometimes the right thing to do. But Bob's information is something I had never fully considered. Given the interaction between the recovery interval, checkpoints, and the lazy writer process, Bob says: "If you've adjusted the recovery interval, you should watch the lazy writer performance counter(s) activity closely." You owe it to yourself to read this new guide from Microsoft and learn more about SQL Server I/O. I enjoyed the piece, and I'm sure you will too.