Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to analyze the structure of SQL Server Page in detail. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The basic unit of SQL Server to store data is Page, the size of each Page is 8KB, and the data file is made up of Page. On the same database, each Page has a unique resource identity, which consists of three parts: db_id,file_id,page_id, for example, 15ID,1 1pur873je 15 is the ID,1 of the database, the ID,8733 of the data file is the number of Page, and the number of Page is incremented from 0. Eight contiguous Page form an Extent, and the allocated (Allocated) space in the data file is divided into integer multiples of the region. A disk IO operation acts at the Page level, and the smallest unit of space allocation is the zone.

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

The log file has no Page structure, it is made up of a series of log records.

First, the structure of Page

Each Page consists of a header (Header), a content (Content) and a row offset (Offset). The header is at the beginning of the Page and occupies 96Bytes, which is used to store system information such as the number of the Page, the type of Page, the Allocation Unit and so on. Note: store up to 8060Bytes data in a single Page.

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

The data rows are stored after the Page Header, the physical storage of the data rows in the Page is out of order, the logical order of the rows is determined by the row offset (Row Offset), the row offset is stored at the end of the Page, each row offset is a Slot, occupying 2B. Row offsets are arranged successively at the end of the Page and are called slot arrays (Slot Array). The row offset stores the offset of the row in reverse order, which means that the offset of the first line is stored in the end Slot of the Page and the offset of the second line is stored in the second Slot at the end of the Page, counting from the end of the Page to the beginning of the Page.

Second, check the Page header information

The Page header information stores the system information of Page, 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 is the ID;page_number of the database file is the number of Page in the current file; print_option refers to the detail level of the print information. The default value is 0, and only Page Header is printed.

For example, look at the header information for the resource identifier: 15 1purl 8777733 Page:

Dbcc traceon (3604) dbcc page (1551) 8777733)

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

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

The meaning of each field in the Page header:

1the number of the page

M_pageId = (1File ID 8777733), the File ID and Page ID in which the Page is located

2 types of page

M_type = 1 the type of Page, the common types are data page and index page:

1-data page, used to represent the leaf node 2-index page of the heap table or clustered index, used to represent the intermediate node of the clustered index or all levels of nodes in the nonclustered index other Page types (the system page is the Page that manages the Page, for example, GAM,IAM, etc.) is as follows:

3-text mix page,4-text tree page for storing large object data of type text 7-sort page, for storing intermediate data results of sorting operations 8-GAM page, for storing global allocation mapping data GAM (Global Allocation Map), each data file is divided into 4GB space blocks (Chunk), each Chunk corresponds to a GAM data page, and the GAM data page appears in a specific location of the data file. A bit maps a zone in the current Chunk. 9-SGAM page, for storing SGAM pages (Shared GAM) 10-IAM page, for storing IAM pages (Index Allocation Map) 11-PFS page, for storing PFS pages (Page Free Space) 13-boot page, for storing database information, only one Page,Page identifier is: db_id:1:9,15-file header page, storing data files, each file in the database has one, and the Page number is 0. 16-diff map page, which stores the mapping of differential backups, indicating whether the data in this area has been modified since the last full backup. 17-ML map page, indicating whether the data in this area has been modified during bulk logging (bulk-Logged) operations since 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 progression of the page in the index

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

For Heap, m_level=0 represents Data Page; for clustered indexes, m_level=0 represents Data Page; for nonclustered indexes, and m_level=0 represents leaf nodes

4, metadata of Page

The metadata of Page is very important. You can view not only the Object where the Page is located, but also the allocation unit and partition ID where the Page is located, which is very useful when troubleshooting deadlocks.

Metadata: AllocUnitId = 72057595905900544, the allocation unit ID (allocation_unit_id) Metadata: PartitionId = 72057594059423744, the partition ID (partition_id) Metadata: IndexId = 1 of the partition where the Page is located, and the index IDMetadata: ObjectId = 1029578706 of the Page, which is used to represent the chain pointer of the object_id5,page of the object to which the Page belongs.

Because the Page of the data table does not exist alone, but is connected by a two-way chained structure

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

6, other header fields

M_slotCnt = 2: the number of Slot on the page, the number of rows of data stored in the Page m_freeCnt = 4513: the space remaining on the page, in bytes, and the remaining 83 bytes m_reservedCnt = 0: the storage space reserved for active transactions, in byte m _ ghostRecCnt = 0: the total number of ghost records on the page (ghost record count) for information about the Page header, you can read "Inside the Storage Engine: Anatomy of a page"

Third, use the metadata of Page to eliminate deadlocks

The metadata of Page includes partition ID, index ID and object ID, which users can use to analyze the causes of deadlocks. The system traces the resource that caused the deadlock, which may be a resource identifier of Page. If you can confirm that the deadlock is caused by unreasonable partitions of the data table or index, you can reset the partition column or set the partition boundary value to split a single partition into multiple partitions, so that the competitive critical resources can be allocated to different partitions and the query request competition for resources can be avoided. And then reduce the occurrence of deadlock.

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

On how to in-depth analysis of the SQL Server Page structure to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.