none
Whats best: Torn Page Detection or Checksum?

Answers

  • 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.

    Saturday, June 14, 2008 12:52 AM
    Answerer

All replies

  • Checksum is what is recommended and the overhead is tiny.
    Friday, June 13, 2008 7:43 PM
    Answerer
  • 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.

    Saturday, June 14, 2008 12:52 AM
    Answerer
  • An example of HIGH overhead: 

    I have an application that stores document references and makes heavy use of Full Text indexes.  Torn page detection runs at about 15% CPU.  With the database under Checksum the server runs at 70% CPU (I'm using two quad cores on an HP Blade). 

    Other OLTP databases on the same server show little difference between Checksum and Torn Page Protection page verification.

    <just because you can, doesn't mean you should>

    Tuesday, July 03, 2012 7:18 PM
  • The BACKUP CHECKSUM option will defenitely take CPU and its directly proportional to the SIZE of the database and IO susystem.

    Test it with development environment before implementing in to production..

    Note: DBCC CHECKDB is not a compensation for BACKUP CHECKSUM because it will take more CPU than CHECKSUM since it check lot and identify the corruption.

    Regards
    Vijayasarathi Dharmaraj

    Wednesday, July 04, 2012 6:50 AM