SQL Server Page type Summary + question Summary

SQL Server contains multiple types of pages to meet data storage requirements. No matter what type of pages, their storage structures are the same. Each data file contains a considerable number of 8 KB pages, that is, each page has 8192bytes available, each page has 96 bytes for the storage of the page header, and the remaining space

It is used to store actual data. At the end of the page, it is an array of data row offsets. It can also be called a "Page slot" array. we can regard a page as a bookcase with square boxes, which row of Data occupies which slot is marked at the end of the page, and the write sequence of the array at the end of the page is reversed, so that the page space can be effectively used.

It is foreseeable that the "slot" on the page is not necessarily stored in order. When a new ID comes in, and this ID is located between the maximum ID and the minimum ID of the page (assuming the leaf page is sorted by ID ), the ID data row is inserted directly to the end of an existing data row. When a query needs to retrieve the row where the ID is located,

The Database Engine finds the "leaf" page from the index page, loads all the pages to the memory, and finds the corresponding rows through the row offset array at the end of the page. The size of the records in the array at the end of the page is stored in the header, and the space occupied by each record in the array for the "Page slot" is 2 bytes.

As far as I know, there are 14 types of SQL Server data files:

Type 1 -- Data Page: the "leaf" PAGE in the clustered index of the Data Page in the heap is located in the Data file in the random dbcc page m_type = 1

Type 2 -- Index Page ):

Non-clustered index the position of non-"leaf" clustered index in the data file is random dbcc page; m_type = 2

Type 3 -- Text Mixed Page ):

LOB data types with a shorter length. multiple types of data are supported. The location where multiple rows are stored in the data file is m_type = 3 in a random dbcc page.

Type 4 -- Text Page (Text Tree Page ):

The location of storing a single LOB row in the data file is random dbcc page m_type = 4

Type 5 -- sorting Page ):

Temporary pages used for sorting are common in TempDB and can be seen when "ONLINE" operations are performed on user data (for example, when the SORT_IN_TEMPDB option is not specified for ONLINE index creation) the location in the data file is random dbcc page; m_type = 19

Type 6 -- global allocation Page ing Page (GAM Page ):

Global Allocation Map, which records whether allocated non-shared (mixed) areas are occupied by a bit in each area. If this value is 1, this area can be used, 0 indicates that it has been used (but not necessarily the storage space is full). The first gam page is always stored on the PAGE where each data file PageID is 2; m_type = 8 in DBCC PAGE

Type 7 -- shared global allocation Page ing Page (SGAM Page ):

Shared Global Allocation Map records whether each Shared (hybrid) zone is occupied by one bit. If the value is 1, there is free storage space in the zone, 0 indicates that the first sgam page is always stored on the PAGE where each data file PageID is 3; m_type = 9 in DBCC PAGE

Type 8 -- index allocation ing Page (IAM Page ):

Index Allocation Map, which records the location of heap tables or Index areas allocated in data files between GAM pages. It is a random dbcc page with m_type = 10.

Type 9 -- idle space tracking Page (PFS Page ):

Page Free Space: the available Space of the tracking Page.
The first pfs page is always stored on the PAGE where each data file PageID is 1, m_type = 11 in DBCC PAGE

Type 10 -- Boot Page ):

Only m_type = 13 in DBCC PAGE

Type 11 -- Server Configuration Page ):

Partial Information in the returned results of sys. configurations is stored. This page only exists on the page where the file ID of the master database is 1PageID 10.

Type 12 -- File Header Page ):

The information of the file is always stored on the PAGE where the PageID of each file is 0. m_type = 15 in DBCC PAGE

Type 13 -- Differential Changed map ):

The first dcm page of the PAGE that is changed after each full backup or differential backup between GAM records m_type = 16 in dbcc page on the PAGE where each data file PageID is 6

Type 14 -- Bulk Change Map ):

Record the change of the first bcm page for large capacity operations after the last backup between each GAM on the PAGE where each data file PageID is 7; m_type = 17 on the DBCC PAGE

The following SQL statement can query the cache page type and quantity in your current database:

Select case page_type WHEN 'diff _ MAP_PAGE 'then' difference change ing (Differential Changed map) 'when' TEXT _ MIX_PAGE 'then' Text hybrid Page (TEXT Mixed Page) the literal 'when' ML _ MAP_PAGE 'then' should be Minimally-Logged, minimizing the 'when' INDEX _ page' then' Index PAGE (INDEX Page) of log records) 'when' FILEHEADER _ page' then' File Header PAGE 'when' DATA _ page' then' Data Page) 'when' IAM _ page' then' index allocation ing PAGE (IAM Page) 'when' GAM _ page' then' global allocation ing PAGE (GAM Page) 'when' BULK _ OPERATION_PAGE 'then' this literal means a large-capacity Change Record 'when' TEXT _ TREE_PAGE 'then' Text Page (TEXT Tree Page) 'when' SGAM _ page' then' share the global allocation PAGE ing Page (sgam page) 'when' PFS _ page' then' idle space tracking Page (pfs page) 'when' BOOT _ page' then' Boot PAGE (BOOT Page) 'else' sorting PAGE? 'End, page_type, COUNT (*) cntFROM sys. dm_ OS _buffer_descriptors WITH (NOLOCK) WHERE database_id = DB_ID () GROUP BY page_type

The result is shown in:

 

According to the above data type introduction, we naturally think thatType 14 -- Bulk Change Map)Is in the graph Query ResultRow 10th BULK_OPERATION_PAGE

But is it true? We set data_type =BULK_OPERATION_PAGETo find out:

Select top 10 * FROM sys. dm_ OS _buffer_descriptors WHERE page_type = 'bulk _ OPERATION_PAGE 'AND DB_ID () = database_id
Order by database_id, FILE_ID, page_id

Query results:

We bring a PageID in the query result to the dbcc page (as we can see, this pageID is notThe first BCM page is displayed on the page where each data file PageID is 7,They are logically consecutive pages.!

We found the aboveM_type = 20!

I searched google and did not find the m_type = 20 record!

Reference: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

However, we can find the following information:

M_type = 17. This data type ML map page records the regions that have been changed since the last backup in "large-capacity log" mode, the first position of this page is always on the 7th page of each file. We fold back to the third line of the first query above, that is, PageType isML_MAP_PAGEThat line,

Import the following SQL statement to query the records of pageID:

This is the legendary first page that always appears7th pages per fileBastard!

We bring PageID7 to dbcc page:

Oh, SHIT! This m_type is 17!

Well, I can only say that I misinterpret the meaning of the literal. It turns out:

Bulk Change Map (BCM), the corresponding PageType in the database cache is actually ML_MAP_PAGE! Minimally-Logged Page!

Who can tell me what the damn BULK_OPERATION_PAGE (m_type = 20) is?

In addition, the unsorted _reorg_page should be the sorting page?