Archive for May 9th, 2010

CHECKSUM and SQL Server May 9th, 2010

Vinod Kumar

Checksum is a new feature introduced in SQL Server 2005+ to aid in data integrity and IO consistency issue detection. Checksum is a way to detect problems and not necessarily take any action. I wrote about CHECKSUM in my previous posts around CHECKSUM Vs Torn Page and many more. The basic functionality is as follows:

  • When the unit of storage is prepared to be written to disk, the checksum is calculated and stamped in the page header
  • Whenever this page is read back, the checksum on the storage unit is re-calculated and compared against the checksum stamped on the page header
  • If there is a difference then appropriate error is raised

A unit of Storage for which checksum is calculated:

  • Database Page
  • Log Block
  • MTF Backup Stream

Checksum is enabled at the database level for Pages and LogBlocks. For backups, you will have to request checksums using the WITH CHECKSUM option. I will discuss in detail on a later blog post along with other Backup related issues.

As soon as the database option for page_verification is turned ON, not all pages are immediately setup with the checksum. Only pages being written after this point will be tagged with the checksum. All databases created in SQL Server 2005+ will have a PAGE_VERIFICATION option set to CHECKSUM. This includes all the system databases: master and model. The tempdb database has PAGE_VERIFICATION option set, as I outlined on my previous post.

A database upgraded from SQL Server 2000 retains the PAGE_VERIFICATION option of TORN_PAGE_DETECTION it had in SQL Server 2000. The upgrade could happen due to restore, attach or setup.

There are a couple of important points that needs to be understood about the page verification. When a page is read into buffer pool a decision is made whether to perform Page Verification based on the database setting. This is clearly explained in the SQL Server IO whitepaper by Robert Dorr.

You will need to use the ALTER DATABASE statement to change the page verification settings on a database. The details are documented in the Books Online topic under the ALTER DATABASE statement TSQL topic.

SELECT name , page_verify_option_desc FROM sys.databases

You can use the DMVs method as above to obtain the page verification option set on a database. There are so many more interesting things to discuss on this topic which I will reserve  for future posts :). Welcome your comments.

Continue reading...