首页 > 代码库 > SQLServer2012 表IAM存储结构探究
SQLServer2012 表IAM存储结构探究
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, 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]) 需要开启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,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 = 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 = http://www.mamicode.com/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,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 = 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 = http://www.mamicode.com/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, 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..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%还是继续问号,期待不远的将来迎来解惑之篇
SQLServer2012 表IAM存储结构探究