The Page Header (A Storage Wednesday Post)

2014-02-19 - General, Series, SQL Server Internals, Storage Wednesday

Introduction

Last week I started the Storage Wednesday series. In this series, always on a Wednesday, I am going to write about the way SQL Server stores and accesses data. The list of posts can be found at the end of last week's post, The Page.

In last week's post we talked about what a page in the context of SQL Server is, when it is used and how to look at one. Today I would like to introduce you to the page header.

The Page Header

Every Page in SQL Server is 8192 bytes in size, no matter what it is used for. Also, every page has a 96 byte header. It is always located at the beginning of the page and contains metadata information that describes what the purpose of the page is. Let's use DBCC PAGE to look at an example. Remember, to use DBCC PAGE you need to turn on trace flag 3604:

The Page Header

This shows a typical page header. DBCC PAGE already picked the header values apart to present them nicely with names. The more important fields are explained below.

m_pageId This is the unique address of the page. We talked about this address last week.
m_headerVersion This identifies the version of the Header. This allows Microsoft to introduce new information or change the layout of the header without invalidating existing databases.
m_type Depending of the purpose that a page serves, it has a different type assigned. You can find the type, encoded as a number, in this field. Data pages for example are of type 1.
m_level If the page is part of an index, this indicate at which level of the index tree it is located. The actual table data is always at level 0.
Metadata: ObjectId If the page is part of a table or indexed view, this contains the object id of that object.
Metadata: IndexId Here you can find the id of the index (or heap) that this page belongs to.
Metadata: PartitionId Every index has at least one partition. This is the id of the partition this page can be found in.
Metadata: AllocUnitId Every partition has at least one allocation unit and every data page belongs to one. Here you can find the allocation unit's id.
m_prevPage SQL Server organizes different pages in double linked lists. If the page is part of such a chain, this field contains the id of the preceding page.
m_nextPage Similar to the previous field, if the page is part of a double linked list, this field contains the id of the page following the current one.
pminlen Here you can find the minimum possible length of a row that can be stored on this page. This value is dependent only on the table or index definition and does not change based on the content of the page.
m_slotCnt The data in a data page is organized in slots. Each slot contains one row. This field indicates how many rows are currently stored in this page.
m_freeCnt The number of free bytes on the page can be found here.
m_freeData This is a pointer to the first free area on the page.
m_reservedCnt Transactions can reserve space on a page. This indicates the total number of bytes reserved on this page by all current transaction.
m_xactReserved The number of bytes reserved by the most recently started transaction.
m_lsn The log sequence number of the last log entry that caused a change to this page can be found here.
m_ghostRecCnt When you delete a row in SQL Server, it is not actually removed from the page, but rather marked as deleted. The actual delete happens asynchronously in a background thread. This field contains the count of records that were marked as deleted but not yet cleaned up.
m_tornBits Here either the page checksum or the torn page detection bits are stored.

Summary

Every page, whether it is used to store table data or it holds internal database metadata, has a 96 byte header. That header contains information about the page's purpose and about the data it holds. It is always located at the beginning of the page.

Categories: General, Series, SQL Server Internals, Storage Wednesday
Tags: , , , , , ,

One Response to The Page Header (A Storage Wednesday Post)

  1. Pingback: Data Pages and the Row Offset Array - sqlity.net

Leave a Reply