Internals Matter: Why You Should Check Your Page Verify Settings

This post is about two things:

1) Your Page Verification Settings are Critical

You should confirm you are running with the correct page verification settings on your SQL Server databases. It’s very important, and I’ll show you why.

2) I Should Attend the Awesome SQLSkills Master Immersion Event - Internals and Performance

My scripts to demo the importance of page verification settings are part of  my entry to win free registration to the SQLSkills Master Immersion Event on Internals and Performance in February. Read more about the training here.

Keep reading to see why I hope this helps me win.

Let’s Start with Page Verification

The default setting for the PAGE_VERIFY database option for SQL 2000 was TORN_PAGE_DETECTION. As Books Online explains here:

When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

SQL 2005 introduced a new page verify database option, CHECKSUM. This was the default for new databases, but the page verification option was not automatically changed when databases were migrated or upgraded from SQL 2000 to SQL 2005. Books Online explains that when you run with CHECKSUM…

the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

It is quite common to find databases on SQL 2005 instances and higher still using the TORN_PAGE_DETECTION setting for page verification. When a change to the setting is suggested, people naturally ask if the extra protection is worth the small amount of extra overhead of computing the checksum.

It certainly is! This post demonstrates why.

To Play Along At Home…

You may want to read my prior post on causing database corruption with a hex editor here. This post explains how to edit data in unsupported ways, and these techniques should never be used near a production server, or a database of any value at all. Make sure to wash your hands before and after using these practices, and having a change of clothes handy is also advisable.

You’ll also need to download the XVI32 hex editor by Christian Maas. No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe

Our Victim Database

Let’s say we have a database with entry data for a contest. The contest is, quite coincidentally, the SQLSkills Master Immersion Event on Internals and Performance in February!

Let’s create our database and schema for our contest entries.

USE master ;
IF DB_ID('CorruptMe') IS NOT NULL
    BEGIN
        ALTER DATABASE CorruptMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE CorruptMe
    END
CREATE DATABASE CorruptMe ;
GO
ALTER DATABASE CorruptMe SET PAGE_VERIFY TORN_PAGE_DETECTION ;
GO
USE CorruptMe ;
--This schema might have an issue or two ;) Just play along.
CREATE TABLE dbo.Contest (
    ContestId INT IDENTITY,
    ContestName NVARCHAR(256),
    CONSTRAINT cxContestId_Contest UNIQUE CLUSTERED (ContestId)
 )
CREATE TABLE dbo.ContestEntry (
    ContestEntryId INT IDENTITY,
    ContestId INT REFERENCES dbo.Contest ( ContestId ) ,
    FirstName NVARCHAR(256) ,
    LastName NVARCHAR(256) ,
    ContestStatus NVARCHAR(256),
    CONSTRAINT cxContestEntryId_ContestEntryId UNIQUE CLUSTERED (ContestEntryId)
)

INSERT  dbo.Contest ( ContestName)
VALUES ( '5-Day Internals and Performance class in Dallas, February 21-25')

INSERT  dbo.ContestEntry ( ContestId, FirstName, LastName, ContestStatus )
VALUES ( 1, 'Hello', 'Kitty', 'Win!' ),
	( 1, 'PePe', 'LePeu', 'Loss' ),
	( 1, 'Kendra', 'Little', 'Sigh' )

It looks like the contest has already been judged. How did everyone do?

SELECT c.ContestName, ce.FirstName, ce.LastName, ce.ContestStatus
FROM dbo.Contest c
JOIN dbo.ContestEntry ce ON c.ContestId=ce.ContestId

Well, it figures. Hello Kitty can be pretty persuasive. Congrats, Kitty.

But What If…

What if something happened to the data? The database is running with TORN_PAGE_DETECTION– let’s see what happens if we change some of the values  in the physical data (mdf) file with a hex editor.

First, take a look at the pages for dbo.ContestEntry:

DBCC IND ('CorruptMe', 'ContestEntry', 1)

On my instance, we have one data page in our clustered index (PageType1). It’s PagePID 147. Let’s see if we can make some changes on that page and if SQL Server will notice.

To do this, take the database offline, and figure out your physical file name and your offset:

USE master; 

ALTER DATABASE CorruptMe SET OFFLINE ;

SELECT physical_name FROM sys.master_files WHERE name = 'CorruptMe' ; SELECT 147 * 8192 AS [My Offset]

Then run the XV134 Hex editor with administrative privileges, and open the file. Use Address -> GoTo and enter your offset as calculated above. (In my case: 1204224).

Here’s the data as I found it on the page (click to view in a new window):

And here is the data after I made quite a few changes in the hex editor, just by typing in the right pane (click to view in a new window):

That couldn’t possibly work, could  it?

Let’s put the database back online and see.

USE master ;
ALTER DATABASE CorruptMe SET OFFLINE ;

SELECT physical_name FROM sys.master_files WHERE name = 'CorruptMe' ;
SELECT 147 * 8192 AS [My Offset]

This returns the following. The values on the page aren’t just in a different order, they’re different than the original values:

What About When I Run the Next CheckDb?

Let’s see:

DBCC CHECKDB('CorruptMe')

This comes back clean. An excerpt from the output is:

What Would Happen if We Were Using Page Verify CHECKSUM?

You can see this by running through the demo again. This time, use CHECKSUM as your database PAGE_VERIFY option, either by default or by running this after it is created:

ALTER DATABASE CorruptMe SET PAGE_VERIFY CHECKSUM;

After editing the page as described in the hex editor, you will be able to bring the database back online successfully. However, as soon as you read the records from the dbo.ContestEntry table, you’ll get an 824 error for the page we edited, like this:

Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x54bc84f5; actual: 0x41298172). It occurred during a read of page (1:147) in database ID …

CheckDb will also alert you to the error the first time it is run.

The Moral of the Story Is: CHECKSUM is the Way to Go For Page Verify

This isn’t about security– this is about you knowing if changes to SQL Server’s files are happening outside of its control, whether by corruption or any other means.

It’s very easy to check if you’re using CHECKSUM. Take a look at your settings today, and make a plan to move to checksum for any databases which need it!

SELECT name FROM sys.databases 
WHERE page_verify_option_desc <> 'CHECKSUM';

Ideally you will include an index rebuild in your upgrade plan, or shortly thereafter– because the checksum needs to be written to each page, this doesn’t start working for a page  until it’s had a write.

For all sorts of more information, see Paul Randal’s post on Myths around Checksums.

Meanwhile, I’m Entering A Contest

Back to our contest for the SQLSkills Master Immersion Event on Internals and Performance in February (info).

My challenge was to express why I’d like to the go to the class, and why I’ll make the best use of the knowledge I gain.

I’d love to go because:

• I’m passionate about learning about internals and performance. Hopefully I’ve demonstrated that here!
• I still have a lot to learn, and there’s no replacement for a great classroom experience.
• I know SQLSkills training is highly interactive and is a great place to ask questions and get real-world, in-depth answers.
• This training will help me grow as a professional.

I’ll make the best use of the knowledge because:

• I love using what I learn to explore more and write blog posts like this– it helps me learn more and share with the community.
• I’m now the *only DBA* at a small, clever internet business. Every bit of expertise I can gain can help me work as part of a great team and make a real difference in our business.
• I’m good at asking questions and helping others learn– I work hard to be a great fellow student and learn together with others.
• I’ve clearly learned enough to be dangerous ;)

What I’d Like to Do at the Training

I would love to blog my way through my first immersion event. I certainly couldn’t (and wouldn’t) capture all the content we cover, but I’d like to capture what the experience is like. I’d also like to characterize how it  helps me think about, and work  with, SQL Server differently.

I hope whomever is selected as the winner chooses to share their experience.