The logical order and physical order of the SQL index and the page of the table

2, but when modifying a table, whether it is a clustered index or a heap of data pages are in natural order to insert data backwards, the offset on the page can be demonstrated. Because the smallest reading unit of a database is a page, the physical order within the page does not matter, just maintaining the logical order of the data within the page.

When data is inserted into a clustered table, the corresponding data page is found according to the index for natural sequential insertion (internal fill factor, so that the data page retains a certain amount of free space),

If the data page is full, the page is paged (the data is moved to a new data page at a certain scale, and the inserted row is inserted in a natural order after the move is complete.) The physical order of the new page may be inconsistent with the logical order).

3. The logical order of the data pages and index pages of the clustered index is then adjusted and can be demonstrated by the DBCC page's Row offset array (slot array).

4, based on the above theory, the production of fragments is reasonable. Because it is a logical adjustment, when inserting data into a table, it is possible or produce pages that have a physical order that is inconsistent with the logical order.

5, based on the 1th, when the table fragmentation is large, you can choose to rebuild the index.

6. The index is divided into reconstruction and reorganization. Fragments have external fragments (when data is inserted, updated, and so on, the logical order of the indexes is inconsistent with the physical order) and internal fragmentation (due to page splits, which are controlled by the fill factor).

----------------------------------------------------

An experiment involves a command:

DBCC IND ({' dbname ' | dbid}, {' ObjName ' | objid},      {nonclustered indid | 1 | 0 |-1 |-2} [, Partition_number]) Get page numbers, file numbers, pages (each piece of data represents a page)

--1: Display all paging information, including IAM paging, data paging, all existing lob paging and row overflow pages, index paging
---1: Displays index paging for all IAM, data paging, and all indexes on the specified object.
---2: Displays all IAM paging for the specified object
---nonclustered indid: Displays all IAM, data paging, and index paging information for an index

----------------------------------------------------------

Property Description:

 --{' dbname ' |dbid} indicates the database name or database ID-----{' objectname ' |objectid} for object name or object ID-----{nonclustered indid|1|0|-1|-2 } represents the inline data paging and the specified object's inline IAM paging information 51--52--1: Displays information for all pagination, including IAM paging, data paging, all existing lob paging and row overflow pages, index paging----------1: Displays all IAM, data paging, and the specified object on the full Index paging for the part index. -----------2: Displays all IAM pagination for the specified object--indid--nonclustered: Displays all IAM, data paging, and index paging information for an index.   The {partition_number}-> is optional for forward compatibility with the DBCC IND command in. It specifies a specific partition number and, if not specified, displays information for all partitions. --The following is the field description for the output of the DBCC IND command: The field Name field describes the Idpagepid page number of the Pagefid paging file Iamf                ID manage the file that contains the IAM page for this page idiampid the IAM page number objectid The Table object that manages the page Idindexid                The index id,0 represents the heap, 1 represents the clustered index, and 2-250 means that the nonclustered index is greater than 250 is the text or image field the partition number where the book P18partitionnumber table or index PartitionID The partition containing the paging idiam_chain_type the type of allocation unit to which the page belongs; inline data, row overflow data, or LOB Data pagetype page type: 1: Data page; 2: index page; 3:lob_mixed_page;4:lo B_tree_page;10:iam page Indexlevel Index level, 0 represents leaf level paging; >0 represents a non-leaf level; NULL represents IAM paging Nextpagefid The file ID of the next paging page in this layer Idnextpagefid the next page ID prevpagefid this layer, and the filename prevpagef ID a page ID on this layer

--You must enable this representation to see the details of the page
DBCC TRACEON (3604)
Go

-------------------------------------------------

DBCC page ([' Database name ' |database ID], file number, page number, print option = [0|1|2|3]) Gets the offset of the page's insider data

The first parameter is the database name or database ID
The second parameter specifies a file number
The second parameter specifies a page number
The Print opt parameter is optional; You can use the following values:
0 default value; Output Buffer Header and page header information
1 Output Buffer header, page header, respectively output each line of information, row offset table
2 Output Buffer header, page header, full page data, row offset table
3 Output Buffer header, page header, do not output each line of information, row offset table; List values for each column separately

----------------------------------------------------------------------

Page Property Description:

Page Header section, which is the first 96 bytes of the page.                141 142 M_pageid = (1:106) current page number 143 144 m_headerversion = 1 version number, always 1145 146 M_type = 10 The current page type, m_type=1 represents the Data page 10:iam page 147 148 m_typeflagbits = 0x0 Data page and index page is 4, the other page is 0149 m_level = 0 The The progression of the page in the index page (b-Tree), 0 for the leaf node 151 m_flagbits = 0x0 Page Flag 153 154 M_objid (allocunitid.idobj) = 277576027 pair  Like ID table id155 156 m_indexid (allocunitid.idind) = 1 Index id,0 stands for heap, 1 for clustered index, 2-250 for nonclustered index greater than 250 is text or Image field book P18157 158 Metadata:allocunitid = 299666199216128 id,sys.allocation_units.allocation_unit_id159 of the storage unit Metadata:partitionid = 299666199216128 The data page is located on the partition number, sys.partitions.partition_id161 162 METADATA:INDEXID = 1 is the same as the M_indexid object's index number, sys.objects.object_id&sys.indexes.index_id163 164 Metadata:objectid = 277576027 is the same as M_objid the Id,sys of the object to which the page belongs.                 objects.object_id165 166 M_prevpage = (0:0) Previous page of the data page 167 168 m_nextpage = (0:0)        The next page of the data page 169 Pminlen = 90 The number of bytes for the fixed-length data is 90 bytes 171 172 m_slotcnt = 2 rows of data in the page, 2 records per page 173 174 m_freecnt                       = 6 of the remaining space in the page, 6 bytes of space left 176 m_freedata = 8182 The position of the free space in the 8182 location of a page 8KB is approximately equal to 8192 bytes of page free space in the position of 8182 177 This page doesn't fit the data. 179 m_reservedcnt = 0 bytes Freed by active transaction 181 M_LSN = (6:524:11) log record number 184 M_xactreserve d = 0 The latest number of bytes added to the m_reservedcnt domain 187 M_xdesid = (0:0) added to M_reservedcnt's most recent transaction id190 m_ghostreccnt = 0 Phantom The number of rows of shadow data 193 m_tornbits = 1 page check digit or is determined by the Database page protection form to replace the page protection bit

... Row offset Array
8176-8177 Slot7
672-8175 free Space
7 (0x7)-607 (0x25f) 607-671
6 (0x6)-542 (0x21e) 542-606
5 (0x5)-467 (0X1D3) 467-541
4 (0x4)-388 (0x184) 388-466
3 (0x3)-309 (0x135) 309-387
2 (0x2)-236 (0xec) 236-308
1 (0x1)-165 (0XA5) 165-235
0 (0x0)-96-164 (0x60)
0-95 PageHeader
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

The logical order and physical order of the SQL index and the page of the table