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