SQL Server 2008 Pages

  • Article

Here are some notes on "SQL Server 2008 Pages" I took while attending an advanced class on SQL Server taught by Paul Randal (https://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 https://msdn.microsoft.com/en-us/library/ms190969.aspx
  • See https://msdn.microsoft.com/en-us/library/ms189051.aspx
  • See https://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: