Ever wonder about torn pages in SQL Server

There's so much good stuff to read on the Internet that I must often simply "lurk" on discussion forums and the like.  (If you didn't already know, a "lurker" is someone who frequents an on-line forum and reads most if not all of the content without posting messages him/herself.)

In one recent forum reading spree, I came across a really interesting discussion among some of my favorite SQL Server MVPs and Microsoft program managers discussing the performance impact of torn page detection on SQL Server.  Here's a paraphrasing:

    Erland Sommarskog (SQL Server MVP): I'm responding to a forum question about the performance impact of torn page detection.  My gut feeling is that torn-page detection shouldn't be such a performance hog, but I have no first-hand experience, so I appreciate comments.

    Peter Byrne (Microsoft , SQL Server Storage Engine): The background checkpoint/lazywriter processes flip the torn page bit during data writes, but it shouldn't affect user threads. Updates don't have a measurable performanc impact, but reads do since the reads must also verify a checksum before accessing the data page.  In any event, torn page detection shoudn't exceed a percentage point or two.

    Pat Wright (SQL Server MVP): And make sure that folks understand that regardless of the presence of disk write cache and RAID that they are not safe from torn pages.  Even on the most redundant and available system, all it takes is an admin moving cables on the SAN switch to mess things up royally and cause torn pages.  Just because you have good hardware doesn't mean you're always safe!

So thanks to Erland, Peter, and Pat!  You've simultaneously helped me understand the performance impact of enabling torn page detection (not more than a percent or two) and that torn pages can happen even in the most available and well-designed environments.  So, be sure to keep torn page detection on to alert you when any major issues occur!

Cheers,

-Kevin