SQLServer2012 表IAM存储结构探究
?Author:zfive5(zidong) ???
Email: zfive5@163.com
引子
国庆节期间，一直在翻阅《程序员的自我修养—链接、装载与库》，这本给我的感觉是越看越乱，但总的来说还不错，一句话--优秀程序员就应该知道每一个字节的意义。
?
看此书前的两本《深入解析SQLServer2008》和《Microsoft SQL Server 2005技术内幕：存储引擎》对IAM解读都是点到为止，让我满脑袋是一堆问号，内心特别想通过IAM找到数据库里的数据，这样才能让我为性能优化打开思路。虽然两本书有些内容重叠，但它们绝对值得收藏和慢慢研读。
?
? 
? ? ?
? ? ? ? ? ? ? ? ? ? ? ??
正文
分析例子与数据
通过下面SQL语句建分析的例子库和数据
|
CREATE TABLE[dbo].[Table6](
??? [a] [int] IDENTITY(1,1)NOTNULL,
??? [b] [char](8000)NULL,
??? [c] [int] NULL
) ON[PRIMARY]
?
DECLARE @V1 INT
SET @V1=1
WHILE @V1<1000000
BEGIN
insert intoTable6(B,C)values(REPLICATE('c',8000),1)
SET @V1=@V1+1
END
?
?
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,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：1533712? Table6第4个IAM page
?
每一个IAM page 都可以通过
dbcc traceon(3604)
dbcc page('A',1)
dbcc page('A',35662,35663,1533712,1)
?
看到内部的数据，其中1,35646 数据如下：
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
?
PAGE: (1:35646)
?
?
BUFFER:
?
?
BUF @0x000000047240BF40
?
bpage = 0x0000000286FAE000????????? bhash = 0x0000000000000000????????? bpageno = (1:35646)
bdbid = 7?????????????????????????? breferences = 0???????????????????? bcputicks = 0
bsampleCount = 0??????????????????? bUse1 = 58643?????????????????????? bstat = 0x9
blog = 0x5adb215a?????????????????? bnext = 0x0000000000000000?????????
?
PAGE HEADER:
?
?
Page @0x0000000286FAE000
?
m_pageId = (1:35646)??????????????? m_headerVersion = 1???????????????? m_type = 10
m_typeFlagBits = 0x0??????????????? m_level = 0???????????????????????? m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 98??? m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044350464???????????????????????????????
Metadata: PartitionId = 72057594040025088??????????????????????????????? Metadata: IndexId = 0
Metadata: ObjectId = 1541580530???? m_prevPage = (0:0)????????????????? m_nextPage = (1:35662)
pminlen = 90??????????????????????? m_slotCnt = 2?????????????????????? m_freeCnt = 6
m_freeData = 8182?????????????????? m_reservedCnt = 0?????????????????? m_lsn = (1236:22589:9)
m_xactReserved = 0????????????????? m_xdesId = (0:0)??????????????????? m_ghostRecCnt = 0
m_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) = CHANGED
ML (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??
0100488b? 0000
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)
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)
?
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)
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)
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',1627839,1)
?
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
?
PAGE: (1:1627839)
?
?
BUFFER:
?
?
BUF @0x000000046F48EC40
?
bpage = 0x000000045C6FC000????????? bhash = 0x0000000000000000????????? bpageno = (1:1627839)
bdbid = 7?????????????????????????? breferences = 0???????????????????? bcputicks = 70
bsampleCount = 1??????????????????? bUse1 = 32444?????????????????????? bstat = 0x9
blog = 0x15ab215a?????????????????? bnext = 0x0000000000000000?????????
?
PAGE HEADER:
?
?
Page @0x000000045C6FC000
?
m_pageId = (1:1627839)????????????? m_headerVersion = 1???????????????? m_type = 1
m_typeFlagBits = 0x0??????????????? m_level = 0? ???????????????????????m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 98??? m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044350464???????????????????????????????
Metadata: PartitionId = 72057594040025088???????????????????????? ???????Metadata: IndexId = 0
Metadata: ObjectId = 1541580530???? m_prevPage = (0:0)????????????????? m_nextPage = (0:0)
pminlen = 8012????????????????????? m_slotCnt = 1?????????????????????? m_freeCnt = 79
m_freeData = 8111?????????????????? m_reservedCnt = 0?????????????????? m_lsn = (2320:17330:8)
m_xactReserved = 0????????????????? m_xdesId = (0:0)??????????????????? m_ghostRecCnt = 0
m_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) = CHANGED
ML (1:1533703) = NOT MIN_LOGGED????
?
DATA:
?
?
Slot 0,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..cccccccccccc
0000000000000014:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000000028:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
000000000000003C:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000000050:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000000064:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
……
0000000000001F04:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000001F18:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000001F2C:?? 63636363 63636363 63636363 63636363 63636363? cccccccccccccccccccc
0000000000001F40:?? 63636363 63636363 01000000 030000???????????? cccccccc.......
?
OFFSET TABLE:
?
Row - Offset???????????????????????
0 (0x0) - 96 (0x60)????????????????
?
?
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
?
?
结论
IAM的结构已经OK 90%，但那10%还是继续问号，期待不远的将来迎来解惑之篇
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
