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 include



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



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:


Comments (1)