A SQL Server DBA myth a day: (17/30) page checksums

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

A few people have suggested some of the myths around page checksums so today is another multi-mythbusting extravaganza! Well, I get excited at least :-)

I described page checksums in depth in the blog post How to tell if the IO subsystem is causing corruptions?

Myth #17: variety of myths around page checksums.

All of them are FALSE

17a) page checksums are enabled automatically when you upgrade from SQL Server 2000 or 7.0

No. You must explicitly enable page checksums on upgraded databases using ALTER DATABASE blah SET PAGE_VERIFY CHECKSUM. Databases that are created on later versions will have page checksums enabled automatically unless you change the setting in the model database – which you shouldn’t.

17b) page checksums are error correcting

No. Page checksums can detect errors in a page but are not like CRC-based checksums in network protocols that can correct single-bit errors.

17c) enabling page checksums kicks off a background task to put a page checksum on each database page

No. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages. This myth goes hand-in-hand with 17d below…

17d) simply reading the pages is enough to put a page checksum on them (e.g. with a backup or DBCC CHECKDB)

No. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.

17e) when a database is changed from torn-page detection to page checksums, all torn-page detection is lost

No. Pages know whether they are protected through torn-page detection, a page checksum, or nothing at all. As mentioned above, pages aren’t changed to a page checksum until they’re physically altered. I went into this in great detail with an example script in the blog post Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

17f) page checksums detect corruption immediately

This myth was suggested for debunking by fellow MVP Gail Shaw (twitter|blog) and is of course untrue. A damaged page cannot be detected until it is read into memory and the buffer pool checks the validity of the page checksum.

7 thoughts on “A SQL Server DBA myth a day: (17/30) page checksums

  1. Hi Paul

    This is a fantastic post (and series) so thanks for sharing:

    > A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.

    I was wondering if you know of any way to determine how many/which pages have a page checksum present. We’ve been using the approach of rebuilding indexes to force a checksum to be written, but it would be very cool to be able to use a DMV or some other query to aggregate numbers on what percentage of our pages have checksums enabled.

    Many thanks

    S

      1. >Nope – the only way to tell is to DBCC PAGE each page and look at the flag bits field to see which have the 0×100 (IIRC) bit set. Tedious.

        I was pretty sure that that would be the case. I think the SQL Server File Layout Viewer tool takes that exact approach – repeatedly calling DBCC PAGE in a for loop and then munching the result.

        Not sure I’ll try that on our live system though ;-)

        Thanks for the reply – much appreciated

        S

  2. Hi Paul,

    I’ve changed some databases from TORN PAGE to CHECKSUM. Now I’m curious to know how many pages were already marked with CHECKSUM after being loaded, changed and persisted again.

    I made some research about this topic but I couldn’t find anything helpful yet.

    Could you provide me some directions about, first, if this verification is possible and, if so, a way to do it?

    Thanks in advance.

    1. There’s no risk and negligible CPU hit from using CHECKSUM – all SQL Server instances in the world should have all databases set to CHECKSUM, including tempdb. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.