首页 > 代码库 > 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])
具体参数描述如下:
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,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存储结构探究