SQL Server Performance

PAGE VERIFY Query - should TornPageDetection ever be used?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Jun 11, 2013.

  1. Trev256b Member

    Should we ever set the PageVerify setting to TornPageDetection?

    1) Does CHECKSUM do everything and more that TornPageDetection?

    2) Someone told me that you only need TornPageDetection if you do not have power management resilience. Is this correct?
  2. Luis Martin Moderator

  3. Trev256b Member

    Summary: Checksum is more comprehensive.
    Torn Page Detection writes a bit for every 512 bytes in the page. This allows you to detect when a page was not successfully written to disk, but does not tell you if the data stored in those 512 bytes is actually correct as a couple of bytes may have been written incorrectly.
    Checksum, on the other hand, calculates a checksum value as the final thing before passing it to the IO system to be written to disk. This guarantees that SQL Server had no part in corrupting the page. When SQL Server reads it back, if a single bit is different, it will be caught, and a checksum error (824) will be generated.
    Torn Page Detection is the more lightweight of the two, but Checksum is safer, and its overhead is still small enough that it's the better option.

Share This Page