Previous to the release of SQL Server 2005 the only page verification method that existed in SQL Server was TornPageDetection. A DBA could set the “page verify” setting on the database either to TornPageDetection or none. With SQL Server 2005, a new page verification method was introduced, Checksum, which provides better protection against corruption than TornPageDetection. In this article I will explore some of the facts about the Checksum page verification method that, in my humble opinion, every SQL Server DBA should know. (Note that all the resources I used to gather this information are provided at the end of this article).
Let’s start with an explanation of why the Checksum page verification method is better than the TornPageDetection method. Kendra Little provides an excellent explanation in her post here, in which she shows that data changed at the page level goes undetected with TornPageDetection enabled. In order to make sense of this, we must first understand how TornPageDetection works. As SQL Server Books Online explains, when torn page detection is specified, a specific 2-bit pattern is saved and stored on the page header for each of the sixteen 512 byte sectors. This allows SQL Server to detect when a page was not successfully written to disk, but does not check for the correctness of the data stored on the 512 byte sectors. That is why Little can successfully modify data in her example without it being detected.
While changing data on a page may go undetected when using TornPageDetection , the Checksum page verification method will, on the other hand, detect the change. When Checksum is specified, SQL Server will calculate a Checksum over the contents of the whole page and that value is stored on the page header when the page is written to disk. When SQL Server reads the page, it will re-compute the Checksum and then compare it to the value on the page header. This process creates some CPU overhead; how much CPU overhead is actually created is not documented anywhere, but I found some places where it is mentioned as somewhere between 1 and 2%.
Something that is very important to know is that enabling Checksum will not discover existing corruption on a page; according to Sunil Agarwal from Microsoft, “If the database page is already corrupt, the Checksum will be computed based on this corrupt data and there will be no way to detect it.” Another important fact is that enabling Checksum on an existing page will not compute a Checksum value for every page on that database. The Checksum value will be added only after the page has been modified and written to disk, which means that even if a database page verification method has been set to Checksum, a Checksum will not be added to the page header when there have not been changes on that page. This is also explained by Sunil in his post here, where it is also mentioned that when a Checksum is available, a backup with the Checksum option will verify the page Checksum during the backup.
While there are other important factors to consider when using Checksum, these considerations create a good start. For more information on the subject and also some cool code please check the following links:
http://www.sqlskills.com/blogs/paul/dbcc-writepage/
http://colleenmorrow.com/2012/06/07/page_verify-checksum-vs-torn-page-detection/
http://www.littlekendra.com/2011/01/25/pageverify/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/29/enabling-checksum-in-sql2005.aspx
http://www.sqlnotes.info/2013/05/02/fix-page-checksum/
http://www.sqlnotes.info/2011/11/23/dbcc-writepage/
http://www.sqlservercentral.com/Forums/Topic663009-266-1.aspx
http://ignaciosalom.com/?p=668
[…] http://logicalread.com/sql-server-checksum-page-verification-is01/#.VTESwJPuOZN […]