(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.
9 thoughts on “A SQL Server DBA myth a day: (17/30) page checksums”
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
Nope – the only way to tell is to DBCC PAGE each page and look at the flag bits field to see which have the 0x100 (IIRC) bit set. Tedious.
>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
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.
There’s no way of doing that except running through all the pages programmatically using DBCC PAGE.
We are about to upgrade from SQL 2005 to SQL 2014 and TORN_PAGE_DETECTION was identified as risk on existing prod SQL 2005. We are looking to switch to CHECKSUM but are concerned about some of the talk out there about the CPU hit. Any guidance on this? Thx
https://blogs.msdn.com/themes/blogs/generic/post.aspx?WeblogApp=sqlserverstorageengine&y=2006&m=06&d=29&WeblogPostName=enabling-checksum-in-sql2005&GroupKeys=
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
Do AGs and it’s automatic page repair impact the decision around page verification? I have a customer with a database which is part of an AG and page verification is set to ‘None’. Unfortunately, I can’t access the secondary to check if that is also the same. Personally, I can’t see any reason why it would be valid to have it set to ‘None’ on the primary.
No it doesn’t. All databases on all servers on all versions 2005+ should have it set to checksum.