In this section, we are going to see PAGE structure and anatomy of a heap page in detail.

Structure of PAGE:

A page comprises 4 sections:

1. Buffer Section: Buffer section shows details about the buffer for the given page.
2. Header Section: 96 bytes header contains details about a page like, page number, page type, number of records, LSN, free bytes etc.
3. Data section: this section contains records (in other words Slots) in hexadecimal format.
4. Row Offset Array Section: contains list of 2 byte hexadecimal values points the location of respective record (slot)

To explore a heap page in details, let’s create a simple table named tExample4 and insert a record.

CREATE TABLE tExample4(
strEmplCode int identity(1001,1),
strFirstName varchar(100),
strDeptCode char(6) NOT NULL,
strAddress varchar(500),
intSalary int)
GO
 
INSERT INTO tExample4(strFirstName, strDeptCode, strAddress, intSalary)
VALUES('AAAAA', 'DEPT01', 'CHENNAI', '12500')
GO 1000
    

As you know, once a simple heap table is created and a record is inserted, I assume below tasks are performed internally.

1. IAM page is allocated for heap table tExample4. (This manage first 4 GB size of tExample4 table)
2. Data page is allocated for first record most probably in mixed extent.
3. Extent allocation details are updated as bit change in GAM/SGAM and respective IAM pages.
4. Page free space details are updated in PFS page (which covers the region where pages of tExample4 table are available)

Here is the DBCC command to view extent allocation details

DBCC TRACEON(3604)
DBCC EXTENTINFO('LearningInternals', 'tExample4')
DBCC TRACEOFF(3604)
    

Output:

Observation:

1. In-row-data Page number (1:241) is allocated for object id 197575742
2. Ext_size tells how many pages were allocated and pg_alloc indicates how many of those pages have been used.

Here is the DBCC command to list IAM page and Data pages allocated for tExample4 table:

DBCC TRACEON(3604)
DBCC IND('LearningInternals', 'tExample4', -1)
DBCC TRACEOFF(3604)
    

Output:

Observation

1. IAM Page responsible for tExample4 is (1,242). Its page type is 10
2. Data page number is : (1:241). Its page type is 1

Other observations:

1. Page type 10 and 1 refers IAM and data pages respectively
2. IndexId for both IAM and data pages is 0. Meaning page is a heap.
3. PagePid (1,241) is referring IAM Page Id (IAMPID) 1,242

Now let’s examine details available in data page number (1, 241).
Syntax:

DBCC PAGE(<DBName>, <FileID>, <PageNumber>, <PrintOption>)

DBCC TRACEON(3604)
DBCC PAGE(LearningInternals, 1, 241, 1)-- WITH TABLERESULTS
DBCC TRACEOFF(3604)
    

Output:(Formatted for better understanding)

Let’s examine each sections of a heap page in detail.

Buffer section:

How to interpret Buffer section:

Buffer section is not actual part of a page structure. This tells the location of page available in buffer cache.  Buffer section lists Buffer address the page, page related details like database id, page number etc.

Header section:

How to interpret Header section:

Here is some key header properties to notice:

m_pageId – Page Number (in File number : page number format)
m_type – 1 refers to data page and 2 refers to index page
M_level – 0 for heap pages and leaf pages of clustered index.
Mainly used to track depth of B-Tree in index structure (clustered/non clustered Index)

For more details on header section, please refer below link.  Paul has explained each property of header page, possible

values and its meaning in detail.

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

Data section:

Data section consists of 3 parts:

Left part	indicates the byte position within the row
Second part	This section contains the actual data in Hexadecimal format. Knowledge of row structure and hex to decimal type conversion interpret the values
Last part	Right column represents ASCII character representation of the data. Only character data is readable.

How to interpret Data section:

To interpret data section, knowledge of row structure is required. Structure of a data row tells how a record is organized in a data page.

Structure of a data row:

Status Byte A

Status byte A	Meaning
Bit 0	Version info. 0 in Sql server 2008
Bit 1 to 3	3 bit value represents following: 1 - forward record 2 - forwarding stub 3 - index record 4 - blob or row-over-flow data 5 - ghost index record 6 - ghost data record 7 - ghost version record
Bit 4	0 or 1 to find NULL bit map is present or not. In SQL 2008 NULL bit map is always present
Bit 5	0 or 1 to find variable length column is present or not
Bit 6	indicates row contains versioning info
Bit 7	Not used in SQL 2008

Status Byte B

only one bit is used to identify whether the record is a ghost forward record

Here is the final piece. Actual record and its equivalent row in page:

Actual record with its properties:

Column	strEmplCode	strFirstName	strDeptCode	strAddress	intSalary
Value	1001	AAAAA	DEPT01	CHENNAI	12500
Data type	int	varchar	Char	varchar	int
Size	4	5	6	7	4
	Fixed length	variable length	Fixed length	variable length	Fixed length

Record interpretation:

Row Offset Array:

How to interpret row offset array section:

The OFFSET TABLE section shows the offset of row, meaning the location where row 0 is present.
In this case row 0 (first record) is present in location (0x60)

Reference:

1. Microsoft Press - SQL Server 2008 Internals
2. http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx