SQLServer2012 表IAM存储结构探究
引子国庆节期间，一直在翻阅《程序员的自我修养—链接、装载与库》，这本给我的感觉是越看越乱，但总的来说还不错，一句话--优秀程序员就应该知道每一个字节的意义。看此书前的两本《深入解析SQLServer2008》和《Microsoft SQL Server 2005技术内幕：存储引擎》对IAM解读都是点到为止，让我满脑袋是一堆问号，内心特别想通过IAM找到数据库里的数据，这样才能让我为性能优化打开思路。虽然两本书有些内容重叠，但它们绝对值得收藏和慢慢研读。
正文分析例子与数据通过下面SQL语句建分析的例子库和数据
CREATE TABLE.( IDENTITY(1,1)NOTNULL, (8000)NULL, NULL) ON
DECLARE @V1 INTSET @V1=1WHILE @V1<1000000BEGIN insert intoTable6(B,C)values(REPLICATE('c',8000),1)SET @V1=@V1+1END
SQL查询后数据如下图：
两个命令两个命令分别是DBCC IND和 DBCC PAGE
DBCC IND 命令用于查询一个存储对象的内部存储结构信息，该命令有4个参数, 前3个参数必须指定。语法如下：
DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid },{ nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
第一个参数是数据库名或数据库ID。
第二个参数是数据库中的对象名或对象ID，对象可以是表或者索引视图。
第三个参数是一个非聚集索引ID或者 1, 0, 1, or 2. 值的含义：
0: 只显示对象的in-row data页和 in-row IAM 页。
1: 显示对象的全部页, 包含IAM 页, in-row数据页, LOB 数据页row-overflow 数据页 . 如果请求的对象含有聚集所以则索引页也包括。
-1: 显示全部IAM页,数据页, 索引页 也包括 LOB 和row-overflow 数据页。
-2: 显示全部IAM页。
Nonclustered index ID:显示索引的全部 IAM页, data页和索引页，包含LOB和 row-overflow数据页。
为了兼容sql server 2000,第四个参数是可选的,该参数用于指定一个分区号.如果不给定值或者给定0, 则显示全部分区数据。.
DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
具体参数描述如下：
dbid: 包含页面的数据库ID
dbname:包含页面的数据库的名称
filenum:包含页面的文件编号
pagenum:文件内的页面
printopt:可选的输出选项；选用其中一个值：
0:默认值，输出缓冲区的标题和页面标题
1:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表
2:输出缓冲区的标题、页面标题(整体输出页面)，以及行偏移量表
3:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表；每一行后跟分别列出的它的列值
需要开启3604跟踪标志.
接下来通过执行
dbcc ind('A','Table6',-1)，
可以看到如下图数据：
1：35646 Table6第1个IAM page
通过执行：
dbcc traceon(3604)dbcc page('A',1,35646,1)
可以得到所有的：
1：35646 Table6第1个IAM page
1：35662 Table6第2个IAM page
1：35663 Table6第3个IAM page
1：1533712Table6第4个IAM page
每一个IAM page 都可以通过
dbcc traceon(3604)dbcc page('A',1,35646,1)dbcc page('A',1,35662,1)dbcc page('A',1,35663,1)dbcc page('A',1,1533712,1)
看到内部的数据，其中1,35646 数据如下：
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:35646)
BUFFER:
BUF @0x000000047240BF40
bpage = 0x0000000286FAE000 bhash = 0x0000000000000000 bpageno = (1:35646)bdbid = 7 breferences = 0 bcputicks = 0bsampleCount = 0 bUse1 = 58643 bstat = 0x9blog = 0x5adb215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000286FAE000
m_pageId = (1:35646) m_headerVersion = 1 m_type = 10m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200m_objId (AllocUnitId.idObj) = 98 m_indexId (AllocUnitId.idInd) = 256Metadata: AllocUnitId = 72057594044350464 Metadata: PartitionId = 72057594040025088 Metadata: IndexId = 0Metadata: ObjectId = 1541580530 m_prevPage = (0:0) m_nextPage = (1:35662)pminlen = 90 m_slotCnt = 2 m_freeCnt = 6m_freeData = 8182 m_reservedCnt = 0 m_lsn = (1236:22589:9)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 2069893598 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:32352) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 94, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = Record Size = 94
Memory Dump @0x00000000139FA060
0000000000000000: 00005e00 00000000 00000000 00000000 00000000..^.................0000000000000014: 00000000 00000000 00000000 00000000 00000000....................0000000000000028: 00000000 01003d8b 00000100 3f8b00000100488b......=.....?.....H.000000000000003C: 00000100 498b000001004a8b 00000100 4b8b0000....I.....J.....K...0000000000000050: 01004c8b 00000100 4d8b0000 0100 ..L.....M.....
Slot 1, Offset 0xbe, Length 7992, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = Record Size = 7992
Memory Dump @0x00000000139FA0BE
0000000000000000: 0000381f 00000000 00000000 00000000 00000000..8.................0000000000000014: 00000000 00000000 00000000 00000000 00000000....................0000000000000028: 00000000 00000000 00000000 00000000 00000000....................000000000000003C: 00000000 00000000 00000000 00000000 00000000....................0000000000000050: 00000000 00000000 00000000 00000000 00000000....................0000000000000064: 00000000 00000000 00000000 00000000 00000000....................0000000000000078: 00000000 00000000 00000000 00000000 00000000....................000000000000008C: 00000000 00000000 00000000 00000000 00000000....................00000000000000A0: 00000000 00000000 00000000 00000000 00000000....................00000000000000B4: 00000000 00000000 00000000 00000000 00000000....................00000000000000C8: 00000000 00000000 00000000 00000000 00000000....................00000000000000DC: 00000000 00000000 00000000 00000000 00000000....................00000000000000F0: 00000000 00000000 00000000 00000000 00000000....................0000000000000104: 00000000 00000000 00000000 00000000 00000000....................0000000000000118: 00000000 00000000 00000000 00000000 00000000....................000000000000012C: 00000000 00000000 00000000 00000000 00000000....................0000000000000140: 00000000 00000000 00000000 00000000 00000000....................0000000000000154: 00000000 00000000 00000000 00000000 00000000....................0000000000000168: 00000000 00000000 00000000 00000000 00000000....................000000000000017C: 00000000 00000000 00000000 00000000 00000000....................0000000000000190: 00000000 00000000 00000000 00000000 00000000....................00000000000001A4: 00000000 00000000 00000000 00000000 00000000....................00000000000001B8: 00000000 00000000 00000000 00000000 00000000....................00000000000001CC: 00000000 00000000 00000000 00000000 00000000....................00000000000001E0: 00000000 00000000 00000000 00000000 00000000....................00000000000001F4: 00000000 00000000 00000000 00000000 00000000....................0000000000000208: 00000000 00000000 00000000 00000000 00000000....................000000000000021C: 00000000 00000000 00000000 00000000 00000000....................0000000000000230: 00fcffff ffffffff ffffffff ffffffff ffffffff....................0000000000000244: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000258: ffffffff ffffffff ffffffff ffffffff ffffffff....................000000000000026C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000280: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000294: ffffffff ffffffff ffffffff ffffffff ffffffff....................00000000000002A8: ffffffff ffffffff ffffffff ffffffff ffffffff....................00000000000002BC: ffffffff ffffffff ffffffff ffffffff ffffffff....................00000000000002D0: ffffffff ffffffff ffffffff ffffffff ffffffff....................00000000000002E4: ffffffff ffffffff ffffffff ffffffff ffffffff....................00000000000002F8: ffffffff ffffffff ffffffff ffffffff ffffffff....................000000000000030C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000320: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000334: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000000348: ffffffff ffffffff ffffffff ffffffff ffffffff....................……0000000000001CE8: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001CFC: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D10: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D24: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D38: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D4C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D60: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D74: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D88: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001D9C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001DB0: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001DC4: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001DD8: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001DEC: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E00: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E14: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E28: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E3C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E50: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E64: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E78: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001E8C: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001EA0: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001EB4: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001EC8: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001EDC: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001EF0: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001F04: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001F18: ffffffff ffffffff ffffffff ffffffff ffffffff....................0000000000001F2C: ffffffff ffffffff ffffffff ............
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
例子分析每一个IAM有两个SLOT，第1个IAM中第1个SLOT 中有8个页指针如下表（其它IAM第1个SLOT对现在的我还是问号)
指针(二进制)
页指针（十进制）
01003d8b 0000
0100 3f8b0000
0100488b0000
0100 498b0000
01004a8b 0000
0100 4b8b0000
01004c8b 0000
0100 4d8b0000
1 35645
1 35647
1 35656
1 35657
1 35658
1 35659
1 35660
1 35661
Table6的每一个IAM Page第2个SLOT，去除前4个字节，接下来是每BIT都是一个代表1个Extend（8个连续的Page）是否被本数据对象使用:
1:被本对象占用，
0:没有被本对象占用
下面列出Table6 中的每一个IAM 第2个SLOT 部分关键数据
第一个IAM
dbcc traceon(3604)dbcc page('A',1,35646,1)0000000000000000:0000381f 00000000 0000000000000000 00000000……
0000000000000230:00fcffff ffffffff ffffffffffffffff ffffffff
……
0000000000001F2C:ffffffff ffffffff ffffffff
FE: 1111 1100
((231H-4)*8-2)*8=35664
如图蓝色框部分
第二个IAM
dbcc traceon(3604)dbcc page('A',1,35662,1)
0000000000000000:0000381f feffffff ffffffffffffffff ffffffff……0000000000001F2C:ffffffff ffffffff ffffffff
FE: 1111 1110
((1F2CH+12-4)*8+1)*8=511240
如图蓝色框部分
第三个IAM
dbcc traceon(3604)dbcc page('A',1,35663,1)0000000000000000:0000381f feffffff ffffffffffffffff ffffffff……0000000000001F2C:ffffffff ffffffff ffffffff
FE: 1111 1110
((1F2CH+12-4+1FC2H+12-4)*8+1)*8=1022472
如图蓝色框部分
第四个IAM
dbcc traceon(3604)dbcc page('A',1,1533712,1)0000000000000000:0000381f faffffff ffffffffffffffff ffffffff…….00000000000005B4:ffffffff ffffffff ffffffff ffffff0000000000
FE: 1111 1010
((1F2CH+12-4+1FC2H+12-4+1F2CH+12-4)*8+1)*8=1533704
如图第一个蓝色框部分，低位第2个"1" BIT，代表了第二个篮框
某条记然后通过DBCCPAGE 可以得到了真正的数据记录，例如最后一条记录
dbcc traceon(3604)dbcc page('A',1,1627839,1)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:1627839)
BUFFER:
BUF @0x000000046F48EC40
bpage = 0x000000045C6FC000 bhash = 0x0000000000000000 bpageno = (1:1627839)bdbid = 7 breferences = 0 bcputicks = 70bsampleCount = 1 bUse1 = 32444 bstat = 0x9blog = 0x15ab215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000045C6FC000
m_pageId = (1:1627839) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200m_objId (AllocUnitId.idObj) = 98 m_indexId (AllocUnitId.idInd) = 256Metadata: AllocUnitId = 72057594044350464 Metadata: PartitionId = 72057594040025088 Metadata: IndexId = 0Metadata: ObjectId = 1541580530 m_prevPage = (0:0) m_nextPage = (0:0)pminlen = 8012 m_slotCnt = 1 m_freeCnt = 79m_freeData = 8111 m_reservedCnt = 0 m_lsn = (2320:17330:8)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 194973556 DB Frag ID = 1
Allocation Status
GAM (1:1533696) = ALLOCATED SGAM (1:1533697) = NOT ALLOCATED PFS (1:1625688) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:1533702) = CHANGEDML (1:1533703) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 8015, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes =NULL_BITMAP Record Size = 8015
Memory Dump @0x000000001214A060
0000000000000000: 10004c1f 944a1800 63636363 63636363 63636363..L..J..cccccccccccc0000000000000014: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000000028: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc000000000000003C: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000000050: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000000064: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc……0000000000001F04: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000001F18: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000001F2C: 63636363 63636363 63636363 63636363 63636363cccccccccccccccccccc0000000000001F40: 63636363 63636363 01000000 030000 cccccccc.......
OFFSET TABLE:
Row - Offset 0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
结论IAM的结构已经OK 90%，但那10%还是继续问号，期待不远的将来迎来解惑之篇
页:
[1]