Getting to Know I/O

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 ), you can learn about I/O requirements for SQL Server database file operations in a new Microsoft white paper, "SQL Server 2000 I/O Basics" ( http://lists.sqlmag.com/t?ctl=E59:7B3DB ) by Bob Dorr, an escalation engineer with SQL Server Product Support Services. PSS produces some of the best information about SQL Server--after all, they're the ones who have to fix it when it's broken! Escalation engineers like Bob have the benefit of direct access to source code and lots of interaction with real-world customers. Bob's paper summarizes I/O-related issues that are important to SQL Server professionals and has a nice blend of helpful, practical SQL Server guidance. The paper doesn't cover some topics (e.g., RAID) in much detail, but it's a good primer that will help you increase system performance and avoid I/O-environment errors.

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.