Page Header

We all know that a data file in SQL Server is logically broken down into Pages, with each Page having the size of 8KB (8192 bytes). 8 such contigious Pages form an Extent. Thus a 1MB data file will have 128 Pages and 16 Extents starting at offset 0. Below is a pictorial representation of a Page.

In this blog I will be focussing around 96 bytes header section of the Page shown above. So let me quickly touch upon the rest of the section of Page anatomy before getting into the details of the Page Header section.

• Page Header : Metadata for the Page.
• Data (pink) : Data that is stored in the page.
• Free Space (white) : Free space in the Page that can be used for additional data.
• Slot array (purple) : Two byte pointer per row, signifying the order of the rows in the page (could be different than the physical order of the row).

In order to examine the header section of a Page we will use the database that we created previously (Page). Connect to the query window and execute the following command
DBCC TRACEON (3604)
DBCC PAGE (Page, 1, 0, 1) -- parameters Database, File Id, Page No & Flag 1 to show header info
When I fire this command, I get the content below
PAGE HEADER:

Page @0x0000000081A02000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7018 m_freeData = 2248
m_reservedCnt = 0 m_lsn = (132:134:3) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 561247307
Explanation for some of the important fields is as below.
m_PageId : The first part identifies the file to which this page belongs and second is the page number
m_headerVersion : Have always seen the page header version as 1.
m_type : Defines the type of the Page. In this case it is 15 which means file header page. Will list down the other page types below.
m_level : Level of the page in the tree.
m_objId & m_IndexId : Identifies the allocation unit to which the page belongs.
m_prevPage : Pointer to the previous Page at the same level in the tree.
m_nextPage : Pointer to the next Page at the same level in the tree.
m_slotCnt : Count of records on the Page
m_freeCnt : Number of bytes of free space on the Page
m_freeData : It is the offset from the start of the page to the first byte after the end of the last record on the page.
m_reservedCnt : It is the number of bytes of free space that has been reserved by active transactions that freed up space on the page. It prevents the free space from being used up and allows the transactions to roll-back correctly.
m_lsn : LSN of the last log record that changed the page.
m_ghostRecCnt : Number of ghost records (residual information left on database pages because of data modification routines in SQL Server) in the Page .
The reason I explained the Page Header here, is that the up coming sections will show you, how this information in the page gets modified as the underlying object to which the page belongs changes.
Let's go a little further to make it a little exciting by taking an example. All my examples will be based on the database Page created previously, unless specified otherwise

• Create table Test in database Page using the script below.

CREATE TABLE Test(col1 int, col2 int);

CREATE CLUSTERED INDEX idx_Test ON Test(col1);

• Insert a row into the table

INSERTINTO Test VALUES (1,10);

• Use DBCC Ind to find the pages allocated to this table. I get the result below. From the result below it is clear that two pages have been allocated to table Test. One is of type 10 (Index Allocation Map : will talk about it later), while the other one is of type 1 (Data Page) with page number 93 (PagePID) in File 1 (PageFID).

DBCC IND(Page,Test,1)

• Now that we have the page number and file id with us, lets use DBCC PAGE command to display the entire content of the Page.

DBCC TRACEON (3604);
DBCC PAGE (Page, 1, 93, 3);

PAGE: (1:93)

BUFFER:

BUF @0x0000000086FA1C80
bpage = 0x000000008604C000 bhash = 0x0000000000000000 bpageno = (1:93)
bdbid = 10 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 53927 bstat = 0xc0000b
blog = 0x432159bb bnext = 0x0000000000000000
PAGE HEADER:

Page @0x000000008604C000
m_pageId = (1:93) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 31 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039959552
Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 0
Metadata: ObjectId = 2121058592 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 12 m_slotCnt = 1 m_freeCnt = 8079
m_freeData = 111 m_reservedCnt = 0 m_lsn = (214:159:19)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 15
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 15
Memory Dump @0x000000000E7EA060
0000000000000000: 10000c00 0a000000 0a000000 020000...............
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 10
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
col2 = 10

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Couple of things to note above are

• In DBCC PAGE command I used the flag 3 instead of 1 as I wanted to see the complete page and not just the header.
• m_type is 1, which signifies that this is a data page.
• m_prevPage & m_NextPage both are 0 as the table currently has only one page allocated to it.
• m_slotCnt is 1 telling there is only 1 row in this page.
• m_freeCnt needs a little bit of calculation. In order to calculate that lets first find out the record size for the row we inserted in this page. As highlighted in pink it is 15 bytes, so the data size in the page is 15 bytes and 2 bytes for the slot array (pointer to this row). If there were 2 rows than slot array would be 4 byte (2 byte per row). So m_freeCnt = 8192 (page size) - 15 (record size) - 2 (slot array) - 96 (page header) = 8079
• m_freeData by definition can be calculated as 96 (page header) + 15 (record size) = 111
• m_lsn (214:159:19) is the last log record that modified this page. The easiest way to see this changing is fire an update statement on this row.

I believe if you had not already seen this, it must be exciting for you, isn't it ? One important point here is that DBCC PAGE and DBCC IND are undocumented commands.