Calculation of the bottom data distribution size of the DBCC page data page heap

1. Total size of rows:

Row_size = fixed_data_size + variable_data_size + null_bitmap + 4 (4 refers to row header overhead)

Cost Definition:

fixed_data_size = Total byte size for all fixed-length columns

Variable_data_size = 2 + (Num_variable_cols x 2) + max_var_size

---to meet the maximum storage space by adjusting the max_var_size

Null_bitmap = 2 + ((Num_cols + 7)/8) ( to discard the remainder oh )

To manage the nullability of a column---preserve the portion of the row called a Null bitmap

page=8k=8*1024=8192 bytes

bytes available per page =8192-96=8096

Below I add data validation by creating a new table:

CREATE TABLE [dbo]. [PageSize]
(
[a] [int] NULL,--4 bytes
[b] [nvarchar] (3500) NULL,--7000 bytes Oh
[C] CHAR (4) NULL,--2 bytes
[d] DATETIME NULL,--8 bytes
[E] Money NULL--8 bytes
) on [PRIMARY]

--Inserting data

insert [PageSize]
values (1,replicate (' A ', 3500), ' KING ', ' 2016-05-12 10:41:41.843 ', 89.12)
insert [PageSize]
values (2,replicate (' B ', 3500), ' KING ' , ', 0)
insert [PageSize]
values (3,replicate (' D ', 3500), ' KING ', ' 2016-05-12 10:41:41.843 ', 89.00)
insert [PageSize]
values (3,replicate (' E ', 3500), ' KING ' , ' 2016-05-12 10:41:41.843 ', 89.1)
insert [PageSize]
values (3,replicate (' F ', 3500), ' KING ', ' 2016-05-12 10:41:41.843 ', 89.2)

Let's count the first line line length:

According to the above formula is calculated OH:

size=4+4+8+8+7004+3+4=7035 (Time for forensic miracles)

Please continue to look down on the face Oh!

--View page information
DBCC TRACEON (3604)

--View information for tables in the library

DBCC Showcontig

DBCC Showcontig is scanning the ' PageSize ' table ...
table: ' PageSize ' (901578250); index id:0, database Id:8
a TABLE-level scan has been performed.
-Number of pages ...........: 6 ........ £ º
-Number of scan zones ............: 3 .......
-zone switching times ...........: 2 ....... £ º
-The average number of pages in each zone ......: 2.0------
-scanning density [best count: Actual Count] ...: 33.33% [1:3]
-area Scan fragment ....: 0.00%------
-The average number of bytes available per page ......: 2231.5-----
-Avg. page density (full) ... ....: 72.43%....

--View the collection of pages in the library
DBCC EXTENTINFO (test,pagesize)

file_id page_id pg_alloc ext_size object_id index_id partition_number partition_id iam_chain_type pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- ----- --------------- ------------------
1 156 1 1 901578250 0 1 72057594039304192 in-row data 0x6300000000000000
1 164 1 1 901578250 0 1 72057594039304192 in-row data 0x6300000000000000
1 165 1 1 901578250 0 1 72057594039304192 in-row data 0x6300000000000000
1 166 1 1 901578250 0 1 72057594039304192 in-row data 0x6300000000000000
1 168 1 1 901578250 0 1 72057594039304192 in-row data 0x6300000000000000
1 169 1 1 901578250 0 1 72057594039304192 in-row data 0x6000000000000000

See page number:

Find the first line page_id 156

DBCC page (test,1,156,3)

Below I will copy out everyone to see: (Here are not ah, not affect to see)

The following blue font: The first line is M_pageid = (1:156) no problem.

You look at the last line again:record size = 7035 the actual record sizes Oh

M_pageid = (1:156) m_headerversion = 1 M_type = 1
m_typeflagbits = 0x0 M_level = 0 m_flagbits = 0x8000
M_objid (allocunitid.idobj) = M_indexid (allocunitid.idind) =
Metadata:allocunitid = 72057594043695104
Metadata:partitionid = 72057594039304192 Metadata:indexid = 0
Metadata:objectid = 901578250 m_prevpage = (0:0) M_nextpage = (0:0)
Pminlen = m_slotcnt = 1 m_freecnt = 1059
M_freedata = 7131 m_reservedcnt = 0 M_lsn = (492:6024:2)
m_xactreserved = 0 M_xdesid = (0:0) m_ghostreccnt = 0
m_tornbits = 0 DB Frag ID = 1

Allocation Status

GAM (1:2) = Allocated SGAM (1:3) = Allocated
PFS (1:1) = 0x63 Mixed_ext Allocated 95_pct_full DIFF (1:6) = CHANGED
ML (1:7) = Not min_logged

Slot 0 Offset 0x60 Length 7035

Record Type = Primary_record record Attributes = Null_bitmap variable_columns
Record Size = 7035

Then look at the DBCC page (test,1,156,3) This command analysis!

PAGE: (1:156) --data page number

BUFFER:

BUF @0x00000003fb178ac0 --In-memory page number

bpage = 0x00000003efb32000 physical page Bhash = 0x0000000000000000 Bpageno = (1:156) --the page number corresponding to the physical file
Bdbid = 8 --The corresponding database id breferences = 0 bcputicks = 0
Bsamplecount = 0 BUse1 = 63157 bstat = 0x10b
Blog = 0x15acc bnext = 0x0000000000000000

PAGE HEADER:

--Page Header 96 bytes

Page @0x00000003efb32000

M_pageid = (1:156)-- page number m_headerversion = 1 M_type = 1 --Data page type, 1: leaf node data for heap table and clustered index
m_typeflagbits = 0x0 M_level = 0 m_flagbits = 0x8000
M_objid (allocunitid.idobj) = table ID m_indexid (allocunitid.idind) =
Metadata:allocunitid = 72057594043695104
Metadata:partitionid = 72057594039304192 Metadata:indexid = 0
Metadata:objectid = 901578250 m_prevpage = (0:0) M_nextpage = (0:0)
Pminlen = m_slotcnt = 1--When the number of page rows is m_freecnt = 1059--the rest of thespace here!
m_freedata = 7131--This is 7035+ header 96 Oh, and the next time you insert the data, the starting position m_reservedcnt = 0 M_lsn = (492:6024:2)
m_xactreserved = 0 M_xdesid = (0:0) m_ghostreccnt = 0
m_tornbits = 0 DB Frag ID = 1

Here you can see the 7131+1059=8290?? --actually plus + line offset pointer array

So 7131+1059+2=8292

Allocation Status

GAM (1:2) = Allocated SGAM (1:3) = Allocated
PFS (1:1) = 0x63 Mixed_ext Allocated 95_pct_full DIFF (1:6) = CHANGED
ML (1:7) = Not min_logged

Slot 0 Offset 0x60 Length

Record Type = Primary_record record Attributes = Null_bitmap variable_columns
Record Size = 7035
Memory Dump @0x0000000012e1a060

DBCC page data page heap underlying data distribution size calculation