SQLSERVER2008存储结构三_堆数据表

1、目录SQL Server 2008连载之存储结构堆数据表1SQL Server 2008连载之存储结构行溢出页面和lob页面8SQL Server 2008连载之存储结构堆数据表堆数据表是没有聚集索引的表。 即数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接。sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连

2、接是记录在 IAM 页内的信息那么堆表是如何存储数据的呢？出于简化的目的，我们先来构造不含任何索引的一张堆数据表，然后从简单到复杂逐步深入探讨。-创建一张数据表，从系统表生成测试数据DROP TABLE testheap-创建一张2个固定长度字段，3个不定长字段的表，其中4个不为空，1个可为空CREATE TABLE testheap( ID INT IDENTITY(1,1) NOT NULL, name VARCHAR(20) NOT NULL, type CHAR(100) NOT NULL, other VARCHAR(50) NOT NULL, describle VARCHAR(5

3、00)-产生1000条随机数据，并插入表中DECLARE i INT SET i=1WHILE i<=1000BEGIN INSERT INTO testheap(name,type,other,describle) VALUES('name'+CAST(i AS VARCHAR(3),REPLICATE(i%4,100),FLOOR(RAND()*10),NULL) SET i=i+1ENDSELECT * FROM testheap-查询该表的IAM页面地址和首页地址SELECT total_pages,used_pages,data_pages, -first_pa

4、ge,root_page,first_iam_page, testdb.dbo.f_get_page(first_page) first_page_address, testdb.dbo.f_get_page(root_page) root_address, testdb.dbo.f_get_page(first_iam_page) IAM_address FROM sys.system_internals_allocation_units WHERE container_id IN (SELECT partition_id FROM sys.partitions WHERE object_i

5、d in (SELECT object_id FROM sys.objects WHERE name IN ('testheap')查询结果如下：total_pagesused_pagesdata_pagesfirst_page_addressroot_addressIAM_address2518171:2240:01:119即SQL Server为该表分配了总计25个页面，实际使用了18个页面，扣除1个IAM管理页面，实际数据页面为17个，IAM管理页面地址为第一个文件的第119页面，数据页面的第一个页面为第一个文件的第224页面。那么如何查看到该表的页面详细分配情况呢？首先

6、通过dbcc page(testdb,1,119,3)可以粗略看到页面分配情况IAM: Single Page Allocations 0x5DAEC08ESlot 0 = (1:224) Slot 1 = (1:127) Slot 2 = (1:175)Slot 3 = (1:200) Slot 4 = (1:226) Slot 5 = (1:201)Slot 6 = (1:228) Slot 7 = (1:203) IAM: Extent Alloc Status Slot 1 0x5DAEC0C2(1:0) - (1:464) = NOT ALLOCATED (1:472) - (1:4

7、80) = ALLOCATED (1:488) - (1:528) = NOT ALLOCATED即SQL Server首先分配了8个混合区页面，其次因为该对象已经超过8页，SQL Server又分配了从第472页到第487页的页面，共计16个页面，然后包括本身的IAM页面，共计25个页面。其次SQL Server还提供了一个更为友好的命令以找到各个类型的页面分布和它们的所在的文件号和页号。DBCC IND('dbname'|dbid,'objectname'|objectID, nonclustered indid|1|0|-1|-2,partition_nu

8、mber)'dbname'|dbid表示数据库名或者数据库ID'objectname'|objectID表示对象名或者对象IDnonclustered indid|1|0|-1|-2表示显示行内数据分页及指定对象的行内IAM分页信息1 ：显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页-1： 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.-2： 显示指定对象的所有IAM分页nonclustered indid：显示所有的IAM、数据分页以及一个索引的索引分页信息。partition_number->可选,为

9、了与中的DBCC IND命令向前兼容.它指定了一个特定分区号,如果不指定,显示所有分区的信息。以下是DBCC IND命令输出结果的字段描述：字段名称字段描述PageFID 索引文件的IDPagePID 索引分页的IDIAMFID 管理该分页的IAM分页所在的文件IDIAMPID 管理该分页的IAM分页的IDObjectID 对象IDIndexID 索引ID，0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引PartitionNumber表或索引所在的分区号码PartitionID 包含该分页的分区IDiam_chain_type 该页所属分配单元类型；行内数据、行溢出数据或Lob数据

10、PageType 分页类型：1数据页面；2索引页面；3Lob_mixed_page；4Lob_tree_page；10IAM页面IndexLevel 索引层级，0 代表叶级别分页 ；>0 代表非叶级别层次； NULL 代表IAM分页NextPageFID 本层下一个分页所在的文件IDNextPagePID 本层下一个分页IDPrevPageFID 本层上一个分页所在的文件IDPrevPagePID 本层上一个分页ID继续为了简化的目的，同时因为模拟的是小型数据表，所以可以忽略相关文件号和iam链类型、分区号（该表暂无行内迁移和lob字段）

11、，我们只需要看看各个数据页之间是否有相互联系、各个页面的类型即可；所以我们构建了一张数据表用以存放dbcc ind命令输出的结果，并有选择性的选择我们想要的字段。CREATE TABLE tablepage( PageFID TINYINT, PagePID INT, IAMFID TINYINT, IAMPID INT, ObjectID INT, IndexID TINYINT, PartitionNumber TINYINT, PartitionID BIGINT, iam_chain_type VARCHAR(30), PageType TINYINT, IndexLevel TINY

12、INT, NextPageFID TINYINT, NextPagePID INT, PrevPageFID TINYINT, PrevPagePID INT );GOTRUNCATE TABLE tablepage;INSERT INTO tablepage EXEC ('DBCC IND(testdb,testheap,1)');SELECT PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel, NextPagePID,PrevPagePID FROM tablepage最终结果如下：我们可以看到SQL Server为该表

13、所使用的页面地址，索引ID、页面类型、索引级别、前后页的关系等等。Pagetype=10为IAM页面，Pagetype=1为数据页面，即17个数据页面，1个IAM页面，与system_internals_allocation_units输出结果一致，每一个数据页面都对应该IAM页面地址，indexid=0表示为堆表，indexlevel=null表示为IAM页面，indexlevel=0表示为叶子节点；而让我们感到有些失望的是每一个页面似乎除了有共同的IAM管理页面之外，相互之间是缺乏联系的。而且从dbcc ind运行的结果来看，每个页面好像也是不连续的，那么首先通过Internals Vie

14、wer插件让我们看一下IAM页的情况吧，前八页是断断续续的分散分布的，而后面的16页却是连续的，再回头看一下tablepage表也印证了这个现象。既然页面与页面之间缺乏联系，那么对堆表数据的访问只能靠IAM页来管理和定位了。SQL Server数据页的结构大体包括三个部分，即标头、数据行和行偏移量。现在让我们正式进入数据页面去看一下数据页面的构造，让我们首先去访问一下该表的数据首页即第224个页面。Dbcc page(testdb,1,224,2)PAGE HEADER部分，即该页面的前96个字节。m_pageId = (1:224)当前页面号码m_headerVersion = 1 版本号，

15、始终为1m_type = 1当前页面类型，m_type=1表示数据页面m_typeFlagBits = 0x4 数据页和索引页为4，其他页为0m_level = 0 该页在索引页（B树）中的级数，0表示为叶子节点m_flagBits = 0x8200页面标志m_objId (AllocUnitId.idObj) = 94 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594044088320 存储单元的ID,sys.allocation_units.allocation_unit_idMetadata: Par

16、titionId = 72057594039107584 数据页所在的分区号，sys.partitions.partition_idMetadata: IndexId = 0对象的索引号，sys.objects.object_id&sys.indexes.index_idMetadata: ObjectId = 133575514该页面所属的对象的id，sys.objects.object_idm_prevPage = (0:0)该数据页的前一页面m_nextPage = (0:0)该数据页的后一页面pminlen = 108 定长数据所占的字节数为108个字节ID INT IDENT

17、ITY(1,1) NOT NULL,type CHAR(100) NOT NULL,共计104个字节，每个定长字段需要2个字节的管理字节m_slotCnt = 62 页面中的数据的行数，每页62条记录m_freeCnt = 293页面中剩余的空间，还剩293字节的空间m_freeData = 7775 从第一个字节到最后一个字节的空间字节数（包括96字节的文件头的长度）m_reservedCnt = 0 活动事务释放的字节数m_lsn = (67:272:3)日志记录号m_xactReserved = 0最新加入到m_reservedCnt领域的字节数m_xdesId = (0:0) 添加到m

18、_reservedCnt的最近的事务idm_ghostRecCnt = 0幻影数据的行数m_tornBits = 1213019927页的校验位或者被由数据库页面保护形式决定分页保护位取代上在页的尾部还有个行偏移矩阵，记录了每条记录的起始位置，每条记录需要2个字节来记录该位置，所以62条记录共计124个维护字节，加上293个剩余空间和实际已使用的7775个字节，刚好8192个字节，即一页。OFFSET TABLE:Row - Offset 61 (0x3d) - 7651 (0x1de3) 60 (0x3c) - 7527 (0x1d67) . 2 (0x2) - 342 (0x156) 1

19、(0x1) - 219 (0xdb) 0 (0x0) - 96 (0x60) 从Offset table和page结构可以知道，第一条记录从第96个字节开始。IDnameTypeotherdescrible1name11111111111111111111111111111111111.8NULL如前文所说，关于数据的存储从第96个字节开始5E43C060: 30006c00 01000000 31313131 31313131 0.l.11111111 5E43C070: 31313131 31313131 31313131 31313131 1111111111111111 5E43C08

20、0: 31313131 31313131 31313131 31313131 1111111111111111 5E43C090: 31313131 31313131 31313131 31313131 1111111111111111 5E43C0A0: 31313131 31313131 31313131 31313131 1111111111111111 5E43C0B0: 31313131 31313131 31313131 31313131 1111111111111111 5E43C0C0: 31313131 31313131 31313131 0500f002 111111111

21、111. 5E43C0D0: 007a007b 006e616d 65313830 006c0002 .180.l.关于数据行的结构我们还可以采用稍微宏观一些的视角来查看。其中状态A为如下说明：bit0:版本信息,在SQL Server 2005/08总是为0bit1-3: 0=(primary record);1=(forwarded record);2=(forwarding stud);3=(index record);4=(溢出数据);5=(ghost索引记录);6=(ghost数据记录)bit4:表示存在NULL位图(在数据行里SQL2005/08总存在NULL位图)bi

22、t5:表示存在变长列bit6:未启用bit7:表示存在幽灵记录 本例中30->00110000 它是一个行属性的位图 从高位存到地位(右边第一位是bit0)，bit4为1即存在变长列的字段，因为在SQLServer2005/2008中总存在NULL位图，所以bit5也为1。状态位B在SQLServer2005/2008中未启用，所以为00记录定长部分的长度为2个字节，是所有定长字段的长度之和加4，该处为int类型4个字节，char(100)为100个字节，再加上4，所以为108，换算成16进制即6c。紧跟其后的为定长字段的内容，即ID字段的4个字节和TYPE字段的100个字节。固定长度的

23、字段数据之后，是该表的总字段数，用两个字节表示，本表包括5个字段所以为05 00。NULL位图:f0->11110000 因为该表只有列 所以只需要看后面个,1表示该行的对应列为NULL或者该位图未使用。本表前4个字段不为空，第5个为空，第6-8未使用。接下来是行内存储数据的变长列的数目:0200->00000000 00000010=2 表示该行存储了列name和other字段的数据。第一变长列数据终止位置为：7a00->00000000 01111010=122=1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)第二变长列数据终

24、止位置:7b00->00000000 01111011=123 实际上就是在前者的基础上加了第二个变长列的字段长度。1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)+len(“8”)第一列变长列的数据: 6e616d 6531换算成字符即'name1'第二列变长列的数据:38换算成字符即8下面让我们将该记录的describle字段更新为非空值后，再看看该记录存储结构相应的变化。UPDATE testheap SET describle='abc' WHERE id=1再次使用dbcc page(testdb,1

25、,224,1)命令00000000: 30006c00 01000000 31313131 31313131 0.l.11111111 00000010: 31313131 31313131 31313131 31313131 1111111111111111 00000020: 31313131 31313131 31313131 31313131 1111111111111111 00000030: 31313131 31313131 31313131 31313131 1111111111111111 00000040: 31313131 31313131 31313131 313131

26、31 1111111111111111 00000050: 31313131 31313131 31313131 31313131 1111111111111111 00000060: 31313131 31313131 31313131 0500e003 111111111111. 00000070: 007c007d 0080006e 616d6531 38616263 .|.name18abc我们不难发现状态A，状态B，定长长度、定长内容和字段总数是没有发生任何变化的。NULL位图部分变成了e0即11100000，表示describle字段即第五个字段不为空了第一个和第二个变长列数据终止

27、位置分别加了2个长度，这是因为当第三个变长列变更为非空后，自动添加了2个字节的第三个字段的维护字段第一个变长列数据终止位置从7a00变更为7c00第二个变长列数据终止位置从7b00变更为7d00新增加的第三个变长列终止位置为8000同时在第一、二列变长列的数据后面新增加了616263，即字符串”abc”还有一个最显著的区别就是该记录的偏移位置显然转到了尾部，即5F1E的位置；但很奇怪的是该记录原来的位置上还保留着原值，并没有删除掉。也就是说对于该记录而言，应该是先删除，然后又添加了一条新纪录，只是把指针指向了新的偏移地址而已。最后观察一下记录是如何删除的DELETE FROM testheap

28、 WHERE ID IN (2,3)当我们对比一下删除前后两条记录的信息，发现基本上原来的位置上数据没有发生任何变化，只是原来的slot1和slot2已经不存在了。即SQL Server认为该数据已经不存在了。3 (0x3) - 465 (0x1d1) 2 (0x2) - 0 (0x0) 1 (0x1) - 0 (0x0) 0 (0x0) - 7775 (0x1e5f)SQL Server 2008连载之存储结构行溢出页面和lob页面行溢出页面USE TESTDBCREATE TABLE testOverFlow( ID INT IDENTITY(1,1), NAME1 VARCHAR(500

29、0), NAME2 VARCHAR(5000)INSERT INTO testOverFlow (NAME1,NAME2)SELECT REPLICATE('A',5000),REPLICATE('AB',2500)UNIONSELECT REPLICATE('B',5000),REPLICATE('BA',2500)SELECT * FROM testOverFlowSELECT type_desc total_pages,used_pages,data_pages, testdb.dbo.f_get_page(first_pa

30、ge) first_page_address, testdb.dbo.f_get_page(root_page) root_address, testdb.dbo.f_get_page(first_iam_page) IAM_address FROM sys.system_internals_allocation_units WHERE container_id IN (SELECT partition_id FROM sys.partitions WHERE object_id in (SELECT object_id FROM sys.objects WHERE name IN ('

31、;testoverflow')DBCC TRACEON(3604)DBCC PAGE(testdb,1,54242,2) -行内数据DBCC PAGE(testdb,1,52343,2) -行迁移数据-同时我们也可以通过dbcc ind获取所有数据页面地址，然后进行页面信息显示TRUNCATE TABLE tablepage;INSERT INTO tablepage EXEC ('DBCC IND(testdb,testOverFlow,1)');SELECT PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel, N

32、extPagePID,PrevPagePID FROM tablepage在NAME2字段之前和普通的行记录信息是一致的，我们只从NAME2字段开始就可以了。NAME2字段在NAME1字段之后，保存了以下内容，即改列的溢出列类型、节点类型、数据库更新次数、字段长度、指向OVERFLOW页的指针。2个字节2个字节2个字节4个字节2个字节4个字节10个字节typelevelTimestampunkownRow Identitfier020000000100 00009d75 00008813 000077cc0000 0100 0000000溢出列类型节点类型Lob数据更新次数ID未知字段长度行溢

33、出指针RowOVerFlow01197322342450001:52343:0让我们再来看一下第52343页看一下行溢出页的数据情况，该页面首先是一个LOB类型的页面，然后主要包括该字段的长度、关联ID，和数据行；很显然行内数据和溢出行数据的关联是通过一个行溢出指针和ID进行的；因此对某个数据查询而言，首先要找到该记录的信息，同时如果发生行溢出，还有根据该列的行溢出指针和关联ID，才能找到整条记录。1个字节1个字节2个字节8个字节4个字节2个字节0800961300009d75 000000000300状态A状态B字段长度IDunkown类型即包含行溢出5014(同变长字段)197322342

34、4未知lob数据行LOB页面从SQL Server 2005版本以后中，新增加了大值数据类型varchar(max)、nvarchar(max)、varbinary(max)。大值数据类型最多可以存储230-1个字节的数据。从行为上来讲这几个数据类型和之前的数据类型 varchar、nvarchar 和 varbinary 相同。按照微软的说法是用这个数据类型来代替之前的text、ntext 和 image 数据类型，它们之间的对应关系为：varchar(max)-text;nvarchar(max)-ntext;varbinary(max)-image对大值数据类型的操作更类似于之前的var

35、char和varbinary之后，因此用法上也比之前的text和image比灵活和便宜。同时触发器也可以直接引用大值数据类型；而之前的text和image是不行的。因此varchar(max)与varchar(n)和text有着千丝万缕的联系。对于varbinary(max)也一样。因为之前我们已经观察过varchar(n)的行为，那么让我们看看这个新的varchar(max)与varchar(n)、text到底有什么不同。CREATE TABLE testVARCHARMAX( ID INT IDENTITY(1,1), name VARCHAR(20), remark VARCHAR(MA

36、X)CREATE TABLE testTEXT( ID INT IDENTITY(1,1), name VARCHAR(20), remark TEXT)INSERT INTO testVARCHARMAX (name,remark)SELECT REPLICATE('A',20),REPLICATE('AB',2500)UNIONSELECT REPLICATE('B',20),REPLICATE('BA',2500)INSERT INTO testTEXT (name,remark)SELECT REPLICATE('

37、A',20),REPLICATE('AB',2500)UNIONSELECT REPLICATE('B',20),REPLICATE('BA',2500)SELECT ,a.type_desc total_pages,used_pages,data_pages, testdb.dbo.f_get_page(first_page) first_page_address, testdb.dbo.f_get_page(root_page) root_address, testdb.dbo.f_get_page(first_iam_p

38、age) IAM_address FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c WHERE a.container_id=b.partition_id and b.object_id=c.object_id AND in ('testVARCHARMAX','testTEXT')运行结果如下：nametotal_pagesused_pagesdata_pagesfirst_addressroot_addressIAM_addresstestVA

39、RCHARMAXIN_ROW_DATA321:2170:01:218testVARCHARMAXLOB_DATA000:00:00:0testTEXTIN_ROW_DATA211:2220:01:223testTEXTLOB_DATA301:2201:2201:221我们很容易发现两者的共同之处，就是两个表都包括LOB_DATA数据类型的分配单元，但是testVARCHARMAX表的LOB_DATA并没有分配页面，而testTEXT表却分配了3个页面；同时testVARCHARMAX表比testTEXT表多了一个数据页面，这是怎么回事呢？让我们首先看看testVARCHARMAX表的第217个

40、数据页面5E4BC060: 30000800 01000000 03004002 002500ad 0.%. 5E4BC070: 13414141 41414141 41414141 41414141 .AAAAAAAAAAAAAAA 5E4BC080: 41414141 41414241 42414241 42414241 AAAAAABABABABABA 让我们通过Internals Viewer插件看一下对该记录的解读与之前的堆表的介绍相比，基本上我们可以看到与varchar(n)的存储结构式完全一致的，在此就不多做叙述了。那么testTEXT表为什么会使用到LOB类型页面呢？我们使用d

41、bcc page命令查看一下。运行dbcc page(testDB,1,222)，我们从第96个字节开始阅读。5D96C060: 30000800 01000000 03004002 00250035 0.%.5 5D96C070: 80414141 41414141 41414141 41414141 .AAAAAAAAAAAAAAA 5D96C080: 41414141 410000d1 07000000 00dc0000 AAAAA. 5D96C090: 00010001 00300008 00020000 00030000 .0. 5D96C0A0: 02002500 35804242 42424242 42424242 .%.5.BBBBBBBBBB 5D96C0B0: 42424242 42424242 42420000 d2070000 BBBBBBBBBB. 5D96C0C0: 0000dc00 00000100 02000000 21212121 .! 从这个角度，我们看到222页面类似于前面所讲到的行溢出页面，即在222页面保留了一个指向行溢出页面的指针运行dbcc page(testDB,