Parse data page header structure: ifobject_id (test) struct (idint, birthdatetime, namechar (10) insertintotestselect1, 2009-11-27, aaaaunionallselect2, 2009-11-27, aaaaexecsp_spaceusedte
Parse data page header structure: if object_id ('test') is not null drop table test go create table test (id int, birth datetime, name char (10 )) insert into test select 1, '2014-11-27 ', 'aaa' union all select 2, '2014-11-27', 'aaa' exec sp_spaceused 'te
Parse the data page header structure:
If object_id ('test') is not null
Drop table test
Go
Create table test (id int, birth datetime, name char (10 ))
Insert into test
Select 1, '2014-11-27 ', 'aaa' union all
Select 2, '2017-11-27 ', 'aaa'
Exec sp_spaceused 'test'
Structure:
Name
Rows
Reserved
Data
Index_size
Unused
Test
2
16 KB
8 KB
8 KB
0 KB
Through dbcc ind (test, test, 0), we can see that the table has two pages, with the page numbers 109 and 89 respectively, where 89 is the data page. We can see the header structure of the data page through dbcc page. Next we will parse the meaning of each field in the header structure.
Dbcc traceon (3604)
Dbcc page (test, 1, 89, 1)
M_pageId = (1: 89)
Data Page number
M_headerVersion = 1
Header file version number, which has been 1 since 7.0
M_type = 1
Page type. 1 is the data page.
M_typeFlagBits = 0x4
The data page and index page are 4, and the other pages are 0.
M_level = 0
The level of this page in the index page (B tree)
M_flagBits = 0x8000
Page flag
M_objId (AllocUnitId. idObj) = 83
M_indexId (AllocUnitId. idInd) = 256
Metadata: AllocUnitId = 72057594043367424
Storage unit ID
Metadata: PartitionId = 72057594038386688
Partition Number of the data page
Metadata: IndexId = 0
Index Number of the page
Metadata: ObjectId = 2089058478
The id of the object to which the page belongs. It can be obtained using object_id.
M_prevPage = (0: 0)
Previous Page of the data page
M_nextPage = (0: 0)
Next page of the data page
Pminlen = 26
Number of bytes occupied by fixed-length data
M_slotCnt = 2
Number of rows of data on the page
M_freeCnt = 8034
Remaining space on the page
M_freeData = 154
The number of spatial bytes from the first byte to the last byte
M_reservedCnt = 0
Number of bytes released by the active transaction
M_lsn = (30: 170: 20)
Log Record Number
M_xactReserved = 0
Number of bytes recently added to the m_reservedCnt Field
M_xdesId = (0: 0)
The latest transaction id added to m_reservedCnt
M_ghostRecCnt = 0
Number of phantom data rows
M_tornBits = 0
The page check bit is replaced by the page protection bit determined by the database page protection form
Note the following important data in the header file:
1. pminlen = 26: in addition to the number of bytes occupied by fixed data in the table, you also need to add four bytes starting from each row.
. That is:
26 = 4 (row overhead) + 4 (space occupied by int) + 8 (space occupied by datetime) + 10 (space occupied by char (10)
2. m_freeData = 154: Header structure of the page file + (additional space required for storing each row of Data + space occupied by the data itself) * (number of rows)
154 = 96 + (7 + 22) * 2 = 96 + 58
3. m_freeCnt = 8034: 8 K for each page, minus m_freeData, and then the space required to record the offset of each row of data (2 bytes per line)
8034 = 8192-154-4
4. m_slotCnt = 2 Number of data rows on the page
Note the value of the m_freeData field. The actual value is the number of spatial bytes from the first byte to the last byte. If the structure of the table has not changed, the data storage is
Header structure (96B)
First line of data
Second row of data
Remaining Space
Line offset
M_freeData value is
The three data parts are empty.
Sum
However, if the table structure is modified and no paging is performed, the data will move backward, and the table Storage situation is changed:
Header structure (96B)
First line of data