I like to share 4 updates before moving on to this section:

1. I 've updated my profile to know more about me and purpose of this blog.
2. I 've republished this part with some updates after getting comments from Seniors. 
    You may notice that I 've removed detailed explaination of header section in this post and given suitable link instead.
3. I missed to add reference section in my last 3 articles (bear with my innocence). This section will be part of my upcoming posts.
4. I am sole responsible for all pictures, every words and examples present in my blog
My blog is going to reflect my little experience in tuning and my understanding from MS Press books on Internals and Tuning.

Need your suggestions/comments/support to give better content in coming days.

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:

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 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:

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