DBCC page 数据页 堆 底层数据分布大小计算
1.行的总大小：
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4（4是指行标题开销）
开销定义：
Fixed_Data_Size = 所有固定长度列的总字节大小
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
---通过调节Max_Var_Size来满足最大存储空间
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)（要抛弃余数哦）
---保留行中称为 Null 位图的部分来管理列的为空性
page=8k=8*1024=8192字节
其中每页可用字节=8192-96=8096
下面我通过创建新表添加数据验证下：
CREATE TABLE [dbo].[PageSize]
(
[a] [int] NULL,--4字节
[b] [nvarchar](3500) NULL,--7000字节哦
[c] CHAR(4)NULL,--2字节
[d] DATETIME NULL,--8字节
[e] MONEY NULL --8字节
) ON [PRIMARY]
--插入数据
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)
我们算下第一行行长度：
根据以上公式算下哦：
size=4+4+8+8+7004+3+4=7035（鉴证奇迹的时候到了）
请继续往下面看哦！
--查看页信息
dbcc traceon(3604)
--查看库中表的信息
DBCC SHOWCONTIG
DBCC SHOWCONTIG 正在扫描 'PageSize' 表...
表: 'PageSize' (901578250)；索引 ID: 0，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 6
- 扫描区数..............................: 3
- 区切换次数..............................: 2
- 每个区的平均页数........................: 2.0
- 扫描密度 [最佳计数:实际计数].......: 33.33% [1:3]
- 区扫描碎片 ..................: 0.00%
- 每页的平均可用字节数.....................: 2231.5
- 平均页密度(满).....................: 72.43%
--查看库中页集合
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
看到page页号：
找到第一行 page_id 为156
dbcc page（test，1,156,3）
下面我来拷贝出来大家看下：（这里都没有截图啊，不影响看）
下面蓝字体：第一行是m_pageId = (1:156) 没问题吧
你再看下最后一行：Record Size = 7035 实际记录大小哦
m_pageId = (1:156) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 88 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043695104
Metadata: PartitionId = 72057594039304192 Metadata: IndexId = 0
Metadata: ObjectId = 901578250 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 28 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
再来看下dbcc page（test，1,156,3）这个命令分析吧！
PAGE: (1:156) --数据页编号
BUFFER:
BUF @0x00000003FB178AC0 -- 内存中页号
bpage = 0x00000003EFB32000 物理页面 bhash = 0x0000000000000000 bpageno = (1:156) --对应物理文件的页面号
bdbid = 8 --对应的数据库ID breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 63157 bstat = 0x10b
blog = 0x15acc bnext = 0x0000000000000000
PAGE HEADER:
--页头96字节
Page @0x00000003EFB32000
m_pageId = (1:156) --页号 m_headerVersion = 1 m_type = 1 --数据页类型，1：堆表和聚集索引的叶子节点数据
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 88 表id m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043695104
Metadata: PartitionId = 72057594039304192 Metadata: IndexId = 0
Metadata: ObjectId = 901578250 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 28 m_slotCnt = 1 --当页行数 m_freeCnt = 1059--这里剩余空间哦
m_freeData = 7131--这是7035+表头96哦 也是下次插入数据的起始位置 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
这里插一句 大家可能看到了7131+1059=8290？？ --实际还要加+行偏移指针数组
因此 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
