http://www.sqlservercentral.com/blogs/livingforsqlserver/2010/12/21/sql-server-storage-internals-part-4-how-to-read-a-heap-page/

Printed 2014/07/30 11:27PM

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

By Ramkumar (LivingForSQLServer), 2010/12/21

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.

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:

ExtentInfo

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:

4_DBCC_IND

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)

 4_Page_Example

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

Buffer section:

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

HeaderSection

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:

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

4StructureOfDataRow

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:

4_record_interpretation1 

 

Row Offset Array:

RowOffsetArray

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

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.