SQL Server 2008 Pages

Here are some notes on "SQL Server 2008 Pages" I took while attending an advanced class on SQL Server taught by Paul Randal (http://sqlskills.com/AboutPaulSRandal.asp).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without the right pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Data Files

• Details about how the data is actually stored inside the database data files (MDF files)
• Different types of 8KB pages in a database file
• How to use the DBCC IND and DBCC PAGE commands to look inside them

A database file is divided into

• Pages – 8 KB each
• Extents – 8 pages, 64KB each – cab be mixed (multiple objects) or uniform (all eight pages used by only one object)
• See http://msdn.microsoft.com/en-us/library/ms190969.aspx
• PFS intervals – around 1 thousand extents, 8 thousand pages, or 64MB
• GAM intervals – around 64 thousand extents or 512 thousand pages or 64 PFS intervals, 4GB each – share extent allocation structures
• See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/08/660071.aspx

Pages include

• A header section (first 96 bytes)
• A slot array (also known as the offset table) growing from the end of the page.
• The actual data sitting between the two
• See http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx
• Page numbers use usually identified as (x:y) where x is the file number and y is the page number inside that file

Types of pages

• File header page, 1 per file (at page 0), attributes of the file
• Boot page, 1 per file (at page 9), attributes of the database
• Global allocation map (GAM) pages, every 4GB (first at page 2), tracks which extents are allocated on a GAM interval
• Shared global allocation map (SGAM) pages, every 4GB (first at page 3), tracks which mixed extents have free space on a GAM interval
• Differential changed map (DCM) pages, every 4GB (first at page 6), tracks which extents were changed since the last full backup on a GAM interval
• Bulk changed map (BCM) pages, every 4GB (first at page 7), tracks which extents were had bulk updates on a GAM interval
• Page free space (PFS) pages, every 64MB (first at page 1), tracks free pages on a PFS interval
• Index allocation map (IAM) pages, up to 3 chains per index/heap (find with DBCC IND) - tracks object allocation
• Data pages, as required - contains data
• Row overflow pages, as required - contains row data that did not fit on a data page
• Two types of LOB pages, as required - contains large objects not stored with row data
• Index pages, as required - contains indexes
• See http://msdn.microsoft.com/en-us/library/ms190969.aspx
• See http://msdn.microsoft.com/en-us/library/ms189051.aspx
• See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/25/646865.aspx

Looking inside a page

• Start with a DBCC IND(database,table,index) command to get information about the associated pages
• Once you identify the page number, use DBCC PAGE to look at it (requires trace flag 3604 to show in results)
• Usage: DBCC PAGE(database name/id, file number, page number, dump style)
• DBCC PAGE dump styles: 0 - header, 1 - header/per-row hex/slot array, 2 - header/full hex, 3 - header/detailed row
• See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
• See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx

Demo

-- Show DBCC PAGE results in messages
DBCC TRACEON(3604)
GO
-- Make sure we're in master
USE MASTER
GO
-- Create TEST database
CREATE DATABASE TEST
GO
-- Make sure we're in TEST
USE TEST
GO
-- Show File Header page (1:0)
DBCC PAGE(TEST, 1, 0, 3)
DBCC PAGE(TEST, 1, 0, 3) WITH TABLERESULTS
GO
-- Show first PFS page (1:1)
DBCC PAGE(TEST, 1, 1, 3)
GO
-- Show first GAM page (1:2)
DBCC PAGE(TEST, 1, 2, 3)
GO
-- Show first SGAM page (1:3)
DBCC PAGE(TEST, 1, 3, 3)
GO
-- Show first DCM page (1:6)
DBCC PAGE(TEST, 1, 6, 3)
GO
-- Show first BCM page (1:7)
DBCC PAGE(TEST, 1, 7, 3)
GO
-- Show Boot page (1:9)
DBCC PAGE(TEST, 1, 9, 3)
GO
-- Create TT table
CREATE TABLE TT (ID INT, NM VARCHAR(50))
GO
-- Insert some rows
INSERT INTO TT (ID, NM)
VALUES (0, 'ZERO'), (1, 'ONE'), (2, 'TWO'), (3, 'THREE'), (4, 'FOUR'),
(5, 'FIVE'), (6, 'SIX'), (7, 'SEVEN'), (8, 'EIGHT'), (9, 'NINE')
GO
-- Look at data for index 0 = heap (two pages should show)
DBCC IND('TEST','TT', 0)
GO
-- Look at first page, IAM (might not be 1:154 in your case)
DBCC PAGE(TEST, 1, 154, 1)
GO
-- Look at second page, data in the heap (might not be 1:153 in your case)
DBCC PAGE(TEST, 1, 153, 3)
DBCC PAGE(TEST, 1, 153, 3) WITH TABLERESULTS
GO
-- Create two indexes, one clustered, one non-clustered
CREATE CLUSTERED INDEX TTID ON TT(ID)
CREATE NONCLUSTERED INDEX TTNM ON TT(NM)
GO
-- Look at data for Index 1 (two pages should show)
DBCC IND('TEST','TT', 1)
GO
-- Look at a clustered index page (might not be 1:155 in your case)
DBCC PAGE(TEST, 1, 155, 3)
DBCC PAGE(TEST, 1, 155, 3) WITH TABLERESULTS
GO
-- Look at data for Index 2 (two pages should show)
DBCC IND('TEST','TT', 2)
GO
-- Look at a non-clustered index page (might not be 1:153 in your case)
DBCC PAGE(TEST, 1, 153, 3)
GO

For more details, check Paul's posts at:

• http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx
• http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx
• http://www.sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-GAM-SGAM-PFS-and-other-allocation-maps.aspx