Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in


I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page:

SQL Server Storage Internals Part 4 - How to read a Heap page

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:

Architecture 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.


strEmplCode int identity(1001,1),

strFirstName varchar(100),

strDeptCode char(6) NOT NULL,

strAddress varchar(500),

intSalary int)



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 EXTENTINFO('LearningInternals', 'tExample4')




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 IND('LearningInternals', 'tExample4', -1)





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).
DBCC PAGE(<DBName>, <FileID>, <PageNumber>, <PrintOption>)


DBCC PAGE(LearningInternals, 1, 241, 1)-- WITH TABLERESULTS


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.

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  is required to

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


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:













Data type













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)

1. Microsoft Press - SQL Server 2008 Internals



Posted by Anonymous on 22 December 2010

Pingback from  Dew Drop &ndash; December 22, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Anonymous on 22 December 2010

Pingback from  Ramkumar (LivingForSqlServer) posts SQL Server Storage Internals Part 4 &#8211; How to read a Heap page | SQL Server Central | sqlmashup

Posted by ChrisBradleyMSSQL on 22 December 2010

Paul, tell me in which book and page all this internal info is fully available, I will then retract my comment.

Posted by Pam Lahoud on 22 December 2010

First off, a lot of it comes directly from books online:

Secondly, you can easily discover this information yourself by using semi-documented features such as dbcc page and doing a little digging and experimenting.

Thirdly, Microsoft is happy to have people like Paul out there spreading his vast knowledge because it makes the community and the product stronger.  As a stockholder, I am thrilled to see how much great information is available to the SQL Server community.  Exposing the internals of SQL Server makes everyone better at their job, and hence better at promoting SQL Server as an enterprise class RDBMS - that's money in all our pockets.

Posted by Anonymous on 22 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, SQL Server Storage Internals Part 4 - How to read a Heap page - LivingForSqlServer         []        on

Posted by Paul Randal on 22 December 2010

Btw Chris, you should check out the effect of your outburst on Twitter. Quite the following you have now.

Posted by Anonymous on 22 December 2010

Pingback from  Theme - Wordpress Video Tutorials

Posted by Anonymous on 22 December 2010

Pingback from  Use it - Wordpress Video Tutorials

Posted by Anonymous on 23 December 2010

Pingback from  Dew Drop &ndash; Holiday Edition &ndash; December 23, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Anonymous on 28 June 2011

It has been a long time since I blogged. I have been blogging in my Organizations internal site on Management

Posted by Anonymous on 13 July 2011

Pingback from  SQL Server Storage Internals Part 4 - How to... | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.