Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server Storage Internals 4
T-SQL 1

Archive · View All
August 2011 5

LivingForSqlServer

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

Aug 9 2011 2:12AM by Ramkumar   

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

Tags: SQL Server Storage Internals,


Ramkumar
366 · 0% · 112
1 Readers Liked this
Jacob Sebastian Liked this on 4/10/2012 5:53:00 AM
Profile · Blog · Facebook · Twitter
1
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server Storage Internals Part 4 - How to read a Heap page" rated 5 out of 5 by 1 readers
SQL Server Storage Internals Part 4 - How to read a Heap page , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]