Day 17 of 31 Days of Disaster Recovery: When are Checksums Written to a Page

Today is day 17 of 31 Days of Disaster Recovery. The series has skipped a couple of days due to real life imposing itself, but we’re getting back on track by digging into the Checksum page verification option and offering up some proof that the checksum value doesn’t get written until the page is written to disk. You may also learn some cool tricks for looking at metadata information. Let’s explore.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery

Checksum Page Verification

I’m going to start out by creating a new database named TestPageVerify, set page verification to NONE, and add a table with some data in it.

-- Create database for testing
Create Database TestPageVerify;
Go

-- Set page verification to none
Alter Database TestPageVerify Set Page_Verify None;
Go

-- Switch to the database
Use TestPageVerify;
Go

-- Create a table with some data in it
Select * Into dbo.AllDBs
From sys.databases;
Go

-- Add a primary key
Alter Table dbo.AllDBs
    Add Constraint PK_AllDBs_DBID primary key (database_id);
Go

Next I will choose a column at random and use the undocumented function sys.fn_PhysLocFormatter(%%physloc%%) to return the file ID and page ID on which the record is located. %%physloc%% is a binary representation of where the row is located and fn_PhysLocFormatter breaks that down and returns the data as file ID, page ID, and slot ID formatted as (File:Page:Slot). After getting the results, I made a note of the location and the key value of the record that I’m going to messing with.

-- Find a random data page in the table
-- (<File>:<Page>:<Row>): (1:277:3)
-- database_id: 4
Select Top(1) sys.fn_PhysLocFormatter(%%physloc%%),
    database_id
From dbo.AllDBs
Order By NewID();
Go

If I use DBCC PAGE to dump the page in its current state, we’ll see that it is not protected. The key data flags to note in the header output are m_flagBits and m_tornBits. You’ll notice in this case that the flag bits are set to 0x0 meaning that checksum is not enabled and written to the page and torn bits are 0 as no value has been written yet.

-- Dump the page (any dump style)
DBCC TraceOn(3604);

DBCC Page(TestPageVerify, 1, 277, 1)

-- From the header output:
-- m_flagBits = 0x0
-- m_tornBits = 0
Go

Now, I’ll enable checksum page verification and recheck the flag bits and torn bits of the page. If you follow it up by running CHECKPOINT manually and rechecking the page, you’ll see that the values don’t change.

Alter Database TestPageVerify Set Page_Verify CheckSum;
Go

-- Dump the page again
-- Still no change
DBCC Page(TestPageVerify, 1, 277, 1)

-- From the header output:
-- m_flagBits = 0x0
-- m_tornBits = 0
Go

The next step is to update our sample record so that the page is dirtied in memory. At this point, dumping the page shows that the checksum value still has not been written and the torn bits is still 0. Running a CHECKPOINT will write the page to disk and cause these values to be updated. The second dump of the page shows that flag bits has been set to 0x200 (checksum page verification is enabled and populated) and torn bits is set to a large integer value.

Begin Tran
    Update dbo.AllDBs
    Set name = name + '_Test'
    Where database_id = 4;
Commit
Go

-- Dump the page again
-- Still no change
DBCC Page(TestPageVerify, 1, 277, 1)

-- From the header output:
-- m_flagBits = 0x0
-- m_tornBits = 0
Go

CHECKPOINT;
Go

-- Dump the page again
-- Still no change
DBCC Page(TestPageVerify, 1, 277, 1)

-- From the header output:
-- m_flagBits = 0x200
-- m_tornBits = 655784296
Go

The next thing I want to test is to add a large, fixed-length column to the table so I can update the record and force it to be moved to a new page. I’ll then use the fn_PhysLocFormatter function again to identify the new page where the record is located.

-- Add a 7600 fixed length column so we can force a page split
Alter Table dbo.AllDBs Add TestVal nchar(3800) null;
Go

Begin Tran
    Update dbo.AllDBs
    Set TestVal = N'Test'
    Where database_id = 4;
Commit
Go

-- Find the same page in the table
-- (<File>:<Page>:<Slot>): (1:282:0)
Select Top(1) sys.fn_PhysLocFormatter(%%physloc%%),
    database_id
From dbo.AllDBs
Where database_id = 4;
Go

Dumping the page header will show that the checksum value is not yet set as both flag bits and torn bits show 0. Running a manual CHECKPOINT and re-dumping the new page shows that the checksum value is not written to the page.

-- Dump the new page
-- No checksum info
DBCC Page(TestPageVerify, 1, 282, 1)

-- From the header output:
-- m_flagBits = 0x0
-- m_tornBits = 0
Go

CHECKPOINT;
Go

-- Dump the page again
-- Still no change
DBCC Page(TestPageVerify, 1, 282, 1)

-- From the header output:
-- m_flagBits = 0x200
-- m_tornBits = 309696659
Go

Nothing left now, but to disable the trace flag we enabled and drop the test database.

-- Disable DBCC Trace
DBCC TraceOff(3604);

-- Cleanup database
Use master;

If DB_ID('TestPageVerify') Is Not Null
    Drop Database TestPageVerify;
Go

Summary

Run through the demo above (also attached below) and you will see that page checksums are not written to the page until the page is written to disk. Simply setting page verification to checksum is not sufficient. My recommendation is to plan to rebuild all indexes and heaps at your next index maintenance window to ensure all data gets rewritten. Beginning with SQL Server 2008, you can rebuild a heap with the ALTER TABLE REBUILD; command.

Demo script: WhenChecksumsAreWritten.zip (1 KB)