SQL server page type summary + query summary

Time:2021-12-26

SQL server contains many different types of pages to meet the needs of data storage. No matter what type of page, their storage structure is the same. Each data file contains a considerable number of pages composed of 8KB, that is, 8192bytes are available for each page, 96bytes are used for the storage of page headers, and the remaining space

It is used to store the actual data. At the end of the page is the data row offset array, which can also be called “page slot” array. We can regard a page as a bookcase with squares. Which row of data occupies which slot is marked at the end of the page, and the writing order of the end of the page group is flashback, so that the page space can be effectively used.

It can be predicted that the “slots” on the page are not necessarily stored in order. When a new ID comes in and the ID is between the maximum ID and the minimum ID of the page (assuming that it is a leaf page sorted by ID), the ID data row can be directly inserted behind the 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 the page into memory, and finds the corresponding row through the row offset array at the end of the page. The record size of the footer array is stored in the header, and the space occupied by each record about the “page slot” in the array is 2bytes.

As far as I know, there are 14 page types for SQL server data files:

Type 1 – data page: the position of the “leaf” page in the clustered index of the data page in the heap in the data file is random, m in the DBCC page_ type=1

Type 2 – index page:

The position of non clustered index and non leaf clustered index in the data file is random in DBCC page m_ type=2

Type 3 – text mixed page:

Short length lob data types, multiple types and multiple lines are stored in the data file at random in the DBCC page m_ type=3

Type 4 – text tree page:

The location of storing a single lob row in the data file is random in DBCC page m_ type=4

Type 5 – sort page:

Temporary pages for sorting operations are common in tempdb. They can also be seen when “online” operations are performed in user data (for example, when the sort_in_tempdb option is not specified for online index creation). The position in the data file is random. M_type = 19 in DBCC page

Type 6 – Global Allocation mapping page (GAM page):

Global allocation map, which records whether the allocated non shared (mixed) area has been used. Each area occupies a bit. If the value is 1, it indicates that the area can be used, and 0 indicates that it has been used (but the storage space is not necessarily full). The first gam page is always stored on the page with pageid 2 of each data file. M_type = 8 in DBCC page

Type 7 – shared global allocation mapping page (SGAM page):

Shared global allocation map, which records whether each shared (mixed) area has been used, and each area occupies a bit. If the value is 1, it indicates that the area has free storage space, 0 indicates that the area is full, and the first SGAM page is always stored on the page with pageid 3 of each data file. M_type = 9 in DBCC page

Type 8 – index allocation mapping page (IAM page):

Index allocation map, which records the allocation of heap tables or indexes between gam pages. The location in the data file is random m in the DBCC page_ type=10

Type 9 – free space tracking page (PFS page):

Page free space to track the free space of the page.
The first PFS page is always stored in the DBCC page on the page with pageid 1 of each data file m_ type=11

Type 10 – boot page:

The information stored in the database range is only on the page with pageid 9 of each database file (file) id 1 in the DBCC page m_ type=13

Type 11 – server configuration page:

Stored sys Some information in the returned results in configurations. This page only exists on the page with file ID 1 and page ID 10 in the master database

Type 12 – file header page:

The information of the file always exists in the DBCC page on the page with pageid 0 of each file m_ type=15

Type 13 – differential changed map:

Record the changed pages after each full backup or differential backup between gams. The first DCM page is m in DBCC page on the page with pageid 6 of each data file_ type=16

Type 14 – bulk change map:

Record the changes of mass operation between each gam since the last backup. The first BCM page is on the page with pageid 7 of each data file in DBCC page_ type=17

The following SQL can query the type and number of cached pages in your current database:

SELECT CASE page_ type WHEN ‘DIFF_ MAP_ Page ‘then’ differential changed map ‘when’ text_ MIX_ Page ‘then’ text mixed page ‘when’ ml_ MAP_ The literal meaning of page ‘then’ should be minimal logged, minimizing the logging of ‘when’ index_ Page ‘then’ index page ‘when’ fileHeader_ Page ‘then’ file header page ‘when’ data_ Page ‘then’ data page ‘when’ Iam_ Page ‘then’ index allocation mapping page (IAM page) ‘when’ Gam_ Page ‘then’ global allocation mapping page (GAM page) ‘when’ bulk_ OPERATION_ Page ‘then’ literally means’ when ‘text, a large volume change record_ TREE_ Page ‘then’ text tree page ‘when’ SGAM_ Page ‘then’ shared global allocation mapping page (SGAM page) ‘when’ PFS_ Page ‘then’ free space tracking page (PFS page) ‘when’ Boot_ Page ‘then’ boot page ‘else’ sort page? ‘ END , page_ type , COUNT(*) cntFROM sys. dm_ os_ buffer_ descriptors WITH ( NOLOCK )WHERE database_ id = DB_ ID()GROUP BY page_ type

The results are shown in the figure below:

 

According to the above data types, we naturally think thatType 14 – bulk change mapIs in the query result of the diagramLine 10 bulk_ OPERATION_ PAGE


But the truth is? We will use data_ type=BULK_OPERATION_PAGEFind out the records of:

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 into DBCC page (in fact, it has been seen here that this pageid is not like that mentioned aboveThe first BCM page is on the page with pageid 7 of each data file,They are logically consecutive pages

We found something abovem_type=20

I searched Google and couldn’t find M_ What record is type = 20!

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

However, we can find the following information:

m_ The data type ml map page with type = 17 records which areas have been changed since the last backup in the “bulk log” mode. The first position of the page is always on page 7 of each file. We return to the third line of the first query above, that is, the pagetype isML_MAP_PAGEMy line,

And bring it into the following SQL to query the record of pageid:

I found that this is the first page in the legend, which always appears inPage 7 of each documentYou bastard!

We bring pageid7 into DBCC page:

Oh,SHIT! M of this_ Type is 17!

Well, I can only say that I misinterpreted the literal meaning of others. It turned out that:

BCM, bulk change map, the corresponding pagetype in the database cache is ml_ MAP_ PAGE! Minimally-Logged Page!

And that damn bulk_ OPERATION_ What is page (m_type = 20), who can tell me?

The other unlinked_ REORG_ Page, should it be a sort page?