Torn_Page_Detection vs. Checksum

  • I have seen a lot about checksum and I understand the differences between the checksum and torn_page_detection. I also know that the checksum adds about a 2 percent load on the server. Does anyone have any data on how much of a load the torn_page_detection adds to the database worload? I would be curious to see the tradeoff between the corruption detection methods.



  • Unmeasurably small - all it's doing is grabbing the first two bits from each of the 16 sectors, storing them in the page header, and then writing an alternating bit pattern into the two bits in each sector. And the reverse when the page is read again. Almost nothing compared to what a page checksum is doing by reading the whole page and adding the contents to a 'checksum'.

    However, torn-page detection does not detect corruption within sectors - only when a write fails some way through. On 2005 onwards you should always have page checksums enabled for this extra benefit - 1-2% CPU shouldn't be an issue (otherwise you've got other problems).

    Some blog posts around this: How to tell if the IO subsystem is causing corruptions? and Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?.

    Paul Randal
    CEO, Check out SQLskills online training! Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • HI Paul,

    That was good aritcal.In My servers database defultly kept in PAGE_VERIFY =NONE ; which is the best option on both

    torn_page_detection vs checksum.

    Please advise me


  • Checksum. Detects far more problems than torn page ever could.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • HI Gail,

    Thank you for the relpy.If i change to CHECKSUM suddenly any effects will happen .


  • Hi kvr,

    I would say no.

    Maybe a slight increase in CPU consumption (as Paul already mentioned) if you have a system which writes/modifies a lot of pages.

    So run your databases all with CHECKSUM enabled and you´re fine.



    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply