SQL Server Page Type rollup + question Rollup _mssql

SQL Server contains several different types of pages to meet the needs of your data store. Regardless of the type of page, their storage structure is the same. Each data file contains a significant number of 8KB pages, each page has 8192bytes available, each page has 96byte for the header storage, and the rest of the space

is used to store the actual data, at the end of the page is the data row offset array, can also be called "page slot" array, we can take a page as a bookcase, which row of data occupies which slot, are marked at the end of the page, and the page Mantissa group write order is flashbacks, so you can effectively use the page space.

It can be foreseen that the "slot" on the page is 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 (a leaf page that is sorted by id), then the ID data row is inserted directly 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 by the row offset array at the end of the page. The record size of the page mantissa group is stored in the page head, and each record of the page slot in the array occupies 2bytes of space.

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

Type 1--data page: The leaf page in the data page clustered index in the heap is in the data file at a random location in the DBCC page m_type=1

Type 2--Index page:

Nonclustered index non-"leaf"-level clustered index the location in the data file is random in the DBCC PAGE m_type=2

Type 3--text Mixed page (text Mixed page):

Shorter-length LOB data types, multiple types, multiple rows stored in the data file are randomly located in the DBCC PAGE m_type=3

Type 4--text page (Text tree page):

Storing a single LOB row in the data file is a random location in the DBCC PAGE m_type=4

Type 5--sort page (Sort page):

Temporary pages in the sort operation are common in tempdb and are visible in the "online" action in user data (for example, when an index is created online without specifying the SORT_IN_TEMPDB option) the location in the data file is random in the DBCC page m_type=19

Type 6--Global Allocation Map page (GAM page):

Global allocation Map, which records whether an allocated unshared (mixed) area has been used to occupy a bit bit in each area, and if the value is 1, the area can be used, 0 indicates that it has been used (but not a certain amount of storage is full) the first GAM page is always stored on a page of PageID 2 per data file m_type=8

Type 7--shared Global Allocation Map page (SGAM page):

Shared Global allocation Map, which records whether each shared (mixed) area has been used to occupy a bit bit per zone, if the value is 1, the zone has free storage space, 0 The description area is full The first SGAM page is always stored in the DBCC page on pages PageID 3 per data file M_type=9

Type 8--Index Allocation mapping page (IAM page):

The index allocation Map, which records the location of the heap table or index between the GAM pages in the data file is random in the DBCC page m_type=10

Type 9--free Space trace page (PFS page):

Page free spaces to track the available space for the page.
The first PFS page is always stored in the DBCC page on pages with 1 pageid per data file m_type=11

Type 10--Boot page:

The information stored in the database scope is only in the DBCC page on pages with PageID 9 per database file (file) ID 1 m_type=13

Type 11--Service Configuration page (Server Configuration page):

Some information is stored in the returned results in sys.configurations the page only exists on a page with a file ID of 1PageID 10 on the master database

Type 12--Header page (file header page):

The information in the file is always stored in the DBCC page on pages PageID 0 per file m_type=15

Type 13--Differential change mapping (differential Changed map):

Record a page that has changed after each full or differential backup between the GAM the first DCM page m_type=16 on the page PageID 6 per data file

Type 14--Bulk Change map (Bulk changes map):

Record the bulk-operation changes after the last backup between each GAM the first BCM page is in the DBCC page on pages PageID 7 per data file m_type=17

The following SQL can query the cached page types and quantities in your current database:

SELECT case Page_type when ' diff_map_page ' THEN ' differential change mapping (differential Changed map) ' When ' text_mix_page ' THEN ' text mixed page (text M Ixed page) ' When ' ml_map_page ' THEN ' this literal meaning should be minimally-logged, minimize logging ' when ' index_page ' THEN ' index page ' when ' FIL Eheader_page ' THEN ' header page (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 ' this literal meaning should be the bulk change record ' when ' text_tree_page ' THEN ' Text page ' when ' sgam_page ' THEN ' Shared Global Allocation Map page (SGAM page) ' When ' pfs_page ' THEN ' free Space Tracking page (PFS page) ' When ' boot_page ' THEN ' start page (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_ty Pe

The results are shown in the following illustration:

As described in the data type above, it is natural to assume that the type 14--the bulk change map (Bulk changes map) is the 10th row in the diagram query result bulk_operation_page

But the truth? We'll find out the records of data_type=bulk_operation_page :

SELECT top *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 brought one of the PageID in the query results to the DBCC page (as we've already seen, this pageid is not like the first BCM page on the page that has PageID 7 per data file, and they are logically contiguous pages !).

We found the m_type=20above!

I searched the Google also did not find m_type=20 is what record!

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

But we can find the following information:

This data type of M_TYPE=17 the ML map page, in bulk-logged mode, records which areas have changed since the last backup, where the first position is always on page 7th of each file, and we retrace the third line of the first query above, that is, the PageType is ml_ the line of Map_page,

and bring it into the following SQL query out of the PageID record:

Found that this is the legendary first page always appears on every file on page 7th of the bastards!

We brought PageID7 into the DBCC PAGE:

oh,shit! this m_type is 17!

Well, I can only say that I have misinterpreted the literal meaning of the original:

BCM, Bulk change map, the corresponding pagetype in the database cache is ml_map_page! Minimally-logged page!

And what is that damned bulk_operation_page (M_TYPE=20), who can tell me?

The other unlinked_reorg_page, should be the sort page?