下面我们向空表中插入数据:
insert into TestTable values('testInRow', 'InRow', 'InRowData', 1, null)
再次查询allocation:
SELECT * FROM [TestDB].[sys].[allocation_units] WHERE container_id='72057594038779904'
得到如下结果:
Total_pages,used_pages,data_pages的值发生改变。
为什么插入了一行数据,会增加两页呢?其中一页是数据页,用来存放用户的数据,另外一页叫做IAM(索引分配映射)页,用来将数据页链接起来。连接方式如下图:
Sytem_internals_allocation_units表存放第一个数据页和第一个IAM页的指针。IAM按照数据页的顺序存放数据页的指针。数据页之间并无直接链接。
为更进一步说明这种存储结构,我们首先从查询Sytem_internals_allocation_units表:
SELECT total_pages,used_pages,data_pages,
first_page,root_page,first_iam_page
FROM sys.system_internals_allocation_units
WHERE container_id ='72057594038779904'
结果如下:
根据页面大小的规则,定义如下函数来计算页面数:
CREATE FUNCTION [dbo].f_get_page(@page_num BINARY(6))
RETURNS VARCHAR(11)
AS
BEGIN
RETURN(CONVERT(VARCHAR(2),(CONVERT(INT,SUBSTRING(@page_num,6,1))*POWER(2,8))+
(CONVERT(INT,SUBSTRING(@page_num,5,1))))+':'+
CONVERT(VARCHAR(11),
(CONVERT(INT,SUBSTRING(@page_num,4,1))*POWER(2,24))+
(CONVERT(INT,SUBSTRING(@page_num,3,1))*POWER(2,16))+
(CONVERT(INT,SUBSTRING(@page_num,2,1))*POWER(2,8))+
(CONVERT(INT,SUBSTRING(@page_num,1,1)))))
END
使用上述函数计算出数据页是79页,IAM页是80页。
接下来使用DBCC PAGE查询IAM页内容:
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn = (20:324:9)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
IAM: Header @0x5950C064 Slot 0, Offset 96
sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (1:0)
IAM: Single Page Allocations @0x5950C08E
Slot 0 = (1:79) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x5950C0C2
(1:0) - (1:376) = NOT ALLOCATED
加亮部分表明了IAM对应的分区信息,以及第一个数据页面指针指向79页。这与我们查询出的first_page值是一致的。一个IAM页面对应8个数据页,当超过8个数据页时,系统会从其对应的4GB空间(约512000个页面)中分配统一区的页面。当数据页超过可分配的页面数时,建立新的IAM页。
接下来查询数据页内容(部分省略):
Slot 0 Offset 0x60 Length 139
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 139
Memory Dump @0x59ECC060
00000000: 30007000 01000000 496e526f 77202020 †0.p.....InRow
00000010: 20202020 20202020 20202020 20202020 †
00000020: 20202020 20202020 20202020 20202020 †
00000030: 20202020 20202020 20202020 20202020 †
00000040: 20202020 20202020 20202020 20202020 †
00000050: 20202020 20202020 20202020 20202020 †
00000060: 20202020 20202020 20202020 01000000 † ....
00000070: 06002002 0082008b 00746573 74496e52 †.. ..‚.‹.testInR
00000080: 6f77496e 526f7744 617461†††††††††††††owInRowData
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x79 Length 9 Length (physical) 9
name = testInRow
Slot 0 Column 3 Offset 0x8 Length 100 Length (physical) 100
type = InRow
Slot 0 Column 4 Offset 0x82 Length 9 Length (physical) 9
summary = InRowData
Slot 0 Column 5 Offset 0x6c Length 4 Length (physical) 4
price = 1
Slot 0 Column 6 Offset 0x0 Length 0 Length (physical) 0
description = [NULL]
这个页面里面的数据正是我们刚才插入的数据。
下面我们再插入一行数据:
insert into TestTable values('testInRow1', 'InRow1', 'InRowData1', 1, null)
现在查询Sytem_internals_allocation_units表中页面的分配,页面没有增加。因为没有产生新的数据页。
接下来我们循环插入1000条数据:
declare @i int
set @i=2
while @i<=1000
begin
insert into TestTable values('testInRow'+CAST(@i as varchar(3)), 'InRow'+CAST(@ias varchar(3)), 'InRowData'+CAST(@i as varchar(3)), @i, null)
set @i = @i+1
end
再次查询页面分配,结果如下:
First_page和first_iam_page没有变化,但是数据页数据增加了。
使用DBCC来查看数据页的信息,可以看到该页每一行的数据。
接下来查看IAM的信息,如下:
Slot 0 = (1:79) Slot 1 = (1:89) Slot 2 = (1:90)
Slot 3 = (1:93) Slot 4 = (1:94) Slot 5 = (1:109)
Slot 6 = (1:110) Slot 7 = (1:114)
IAM: Extent Alloc Status Slot 1 @0x592EC0C2
(1:0) - (1:176) = NOT ALLOCATED
(1:184) - (1:192) = ALLOCATED
(1:200) - (1:376) = NOT ALLOCATED
每一个指针都指向一个数据页。当分配数据页超过8个混合分区后,系统会为数据表分配统一分区。这里,系统为数据表分配184~191, 192~199两个统一分区。每个分区包含8页,加上8个混合分区的页面,一共是25个页面。这25个页面中,使用了19个数据页,加上1个IAM页共使用了20个页面。因为统一分区是顺序分配的, 所以可以计算出从195~199的页面没有被使用。用DBCC可以验证这个推算。
下面我们把插入的数据删除,然后再查看IAM的页面分配情况,发现页面分配不会因为数据删除而改变,数据页内仅仅是将数据清空而已。
PAGE: (1:89)
BUFFER:
BUF @0x0438E120
bpage = 0x06036000 bhash = 0x00000000 bpageno = (1:89)
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 237 bstat = 0xc00009
blog = 0x21bb7979 bnext = 0x00000000
PAGE HEADER:
Page @0x06036000
m_pageId = (1:89) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8208
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 112 m_slotCnt = 53 m_freeCnt = 7990
m_freeData = 7689 m_reservedCnt = 7593 m_lsn = (221:265:61)
m_xactReserved = 7593 m_xdesId = (0:92341) m_ghostRecCnt = 0
m_tornBits = -1698770727
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.