SQL Server data page header Structure Analysis

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.