SQLServer2012 表IAM存储结构探究，sqlserver2012iam
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 = 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 = 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%还是继续问号，期待不远的将来迎来解惑之篇
复制表结构的通用存储过程 -- Transfer对象的重要属性 -- 1. 属性 属性名 类型 描述--------------------------------- ------------------- --------------------CopyAllDefaults Boolean 所有默认值CopyAllObjects Boolean 所有对象CopyAllRules Boolean 所有规则CopyAllStoredProcedures Boolean 所有存储过程CopyAllTables Boolean 所有表CopyAllTriggers Boolean 所有触发器CopyAllUserDefinedDatatypes Boolean 所有用户自定义类型CopyAllViews Boolean 所有视图CopyData Boolean 所有数据DestDatabase String 目标对象数据库DestLogin String 目标数据库登陆用户名DestPassword String 目标数据库登陆密码DestServer String 目标服务器DestUseTrustedConnection Boolean 用户信任连接DropDestObjectsFirst Boolean 是否先删除目标对象IncludeDependencies Boolean 是否包含依靠对象ScriptType Boolean 脚本类型 -- 2. 重要方法: 方法名称 功能描述--------------------------- --------------------------AddObject 增加对象AddObjectByName 通过对象名称增加对象 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[P_CopyDB]GO/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移 存储过程实现源数据库到目标数据库的对象和数据的复制 要求源数据库和目标数据库在同一服务器 如果是要实现不同服务器之间的复制，则需要增加验证信息--邹建 2005.07(引用请保留此信息)--*//*--调用示例 CREA......余下全文>>
你试一下这样做，转储成为sql文件，然后到另外的数据库，运行sql文件