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