SQL Server Page Structure in-depth analysis, SQL in-depth analysis

SQL Server Page Structure in-depth analysis, SQL in-depth analysis

The basic unit for SQL Server to store data is Page. The size of each Page is 8 KB, and the data file is composed of pages. On the same database, each Page has a unique resource ID, which consists of three parts: db_id, file_id, and page_id. For example, 15: 1: 8733,15 is the database ID, 1 is the ID of the data file, 8733 is the Page number, and the Page number increases sequentially from 0. Eight consecutive pages form a partition (Extent). The Allocated space in the data file is divided into an integer multiple of the partition. One disk IO operation acts on the Page level, while the smallest unit for space allocation is the partition.

Pages are used to store data. Different types of pages store different data, and the structure of pages is different. Some pages are used to store Data. Some pages are used to store intermediate nodes in the Index structure, called Index Page. Some pages are used by the SQL Server storage engine, A system Page is used to manage pages. This article focuses on Data Page and Index Page, which are related to Data tables.

A log file does not have a Page structure. It consists of a series of log records.

1. Page Structure

Each Page consists of the Header, Content, and Offset. the Header is at the beginning of the Page and occupies 96 Bytes for storing the Page number, page type, Allocation Unit, and other system information. Note: A maximum of bytes of data can be stored in a single Page.

The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB ).

After the data rows are stored in the Page Header, the physical storage of the data rows in the Page is unordered, and the logical order of the rows is determined by the Row Offset, row offset is stored at the end of the Page. Each row offset is a Slot that occupies 2B. The row offset is arranged consecutively at the end of the Page, which is called the Slot Array ). The row offset stores the row offset in reverse order, which means counting starts from the end of the Page and the offset of the first row is stored in the Slot at the end of the Page, the offset of the second row is stored in the second Slot at the end of the Page.

2. view Page header information

The Page header information stores the Page system information, which can be viewed using informal commands:

DBCC PAGE(['database name'|database id], file_id, page_number, print_option = [0|1|2|3] )

Parameter: file_id indicates the ID of the database file, page_number indicates the number of the Page in the current file, and print_option indicates the details of the printed information. The default value is 0. Only the Page Header is printed.

For example, to view the header information of the resource identifier: 15: 1: 8777733 Page:

dbcc traceon(3604)dbcc page(15,1,8777733)

In my database, the header information of the Page (removed Buffer data) is as follows,

PAGE: (1:8777733)PAGE HEADER:Page @0x0000005188B02000m_pageId = (1:8777733)    m_headerVersion = 1     m_type = 1m_typeFlagBits = 0x0    m_level = 0       m_flagBits = 0x220m_objId (AllocUnitId.idObj) = 28503 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057595905900544        Metadata: PartitionId = 72057594059423744        Metadata: IndexId = 1Metadata: ObjectId = 1029578706  m_prevPage = (1:8777732)   m_nextPage = (1:8777734)pminlen = 16      m_slotCnt = 2      m_freeCnt = 4513m_freeData = 3675     m_reservedCnt = 0     m_lsn = (1212327:16:558)m_xactReserved = 0     m_xdesId = (0:799026688)   m_ghostRecCnt = 0m_tornBits = -1518328013   DB Frag ID = 1      Allocation StatusGAM (1:8690944) = ALLOCATED   SGAM (1:8690945) = NOT ALLOCATED PFS (1:8775480) = 0x40 ALLOCATED 0_PCT_FULL       DIFF (1:8690950) = CHANGEDML (1:8690951) = NOT MIN_LOGGED

Meanings of fields in the Page header:

1. Page number

M_pageId = (1: 8777733), the File ID and Page ID of the Page

2. Page type

M_type = 1, Page type. Common types are data pages and index pages:

1-data page, used to indicate the leaf nodes of the heap table or clustered Index
2-index page: indicates the intermediate node of the clustered index or all levels of nodes in the non-clustered index.
Other Page types (system pages are pages that manage pages, such as GAM and IAM) are as follows:

3-text mix page and 4-text tree page are used to store big object data of the text type.
7-sort page, used to store intermediate data results of sorting operations
8-GAM page, used to store Global Allocation ing data GAM (Global Allocation Map). Each data file is divided into 4 GB space blocks (Chunk ), each Chunk corresponds to a GAM data page. The GAM data page appears at a specific location in the data file, and a bit maps to a zone in the current Chunk.
9-SGAM page, used to store SGAM pages (Shared GAM)
10-IAM page, used to store IAM pages (Index Allocation Map)
11-PFS page, used to store PFS pages (Page Free Space)
13-boot page, used to store database information. There is only one Page, and the Page identifier is db_id: 1: 9,
15-file header page: stores data in the data file. Each file in the database has one, and the Page number is 0.
16-diff map page stores the ing of differential backups, indicating whether the data in the zone has been modified since the previous full backup.
17-ML map page indicates whether the data in this area has been modified during bulk-Logged operation after the last backup, this is what allows you to switch to bulk-logged mode for bulk-loads and index rebuilds without worrying about breaking a backup chain.
18-a page that's be deallocated by dbcc checkdb during a repair operation.
19-the temporary page that alter index... REORGANIZE (or dbcc indexdefrag) uses when working on an index.
20-a page pre-allocated as part of a bulk load operation, which will eventually be formatted as a 'real' page.

3. The level of the Page in the Index

The index Level of the data page in the index. m_level = 0 indicates that the data page is at Leaf Level.

For Heap tables, m_level = 0 indicates Data Page;
For clustered indexes, m_level = 0 indicates Data Page;
For non-clustered indexes, m_level = 0 indicates the leaf node.

4. Page metadata

The metadata of the Page is very important. It can not only view the Object where the Page is located, but also view the allocation unit and partition ID of the Page. It is very useful in deadlock troubleshooting.

Metadata: AllocUnitId = 72057595905900544, the allocation unit ID of the Page (allocation_unit_id)
Metadata: PartitionId = 72057594059423744, ID of the partition where the Page is located (partition_id)
Metadata: IndexId = 1, index ID of the Page
Metadata: ObjectId = 1029578706, used to indicate the object_id of the object to which the Page belongs
5. page Link pointer

Because the Page of a data table does not exist independently, it is connected through a two-way chain structure,

M_prevPage = (1: 8777732): used to represent the previous page (FileID: PageID)
M_nextPage = (1: 8777734): used to represent the next page (FileID: PageID)

6. Other header fields

M_slotCnt = 2: Number of slots in the Page, used for the number of data rows stored in the Page
M_freeCnt = 4513: The remaining space on the page, in bytes, with 83 bytes left
M_reservedCnt = 0: storage space reserved for active transactions, in bytes
M_ghostRecCnt = 0: Total number of ghost records on the page (ghost record count)
For information about the Page header, read "Inside the Storage Engine: Anatomy of a page";

3. Use Page metadata to eliminate deadlocks

The Page metadata includes the partition ID, index ID, and Object ID. You can use the metadata to analyze the cause of the deadlock. The system traces the resource that generates the deadlock, which may be a Page Resource Identifier. if you can confirm that the deadlock is caused by unreasonable partitions of the data table or index, You can reset the partition column, alternatively, you can set the partition boundary value to split a single partition into multiple partitions. In this way, you can allocate competing critical resources to different partitions to avoid resource competition from query requests, this reduces the occurrence of deadlocks.

Metadata: PartitionId, the partition ID (partition_id) of the partition where the Page is located );
Metadata: IndexId, index ID of the Page;
Metadata: ObjectId, used to represent the object_id of an object;