加载中…
正文 字体大小:

SQL Server数据库存储结构之IAM与数据页

(2011-08-25 16:53:56)
标签:

it

分类: 技术

下面我们向空表中插入数据:

insert into TestTable values('testInRow', 'InRow', 'InRowData', 1, null)

再次查询allocation

SELECT *  FROM [TestDB].[sys].[allocation_units] WHERE container_id ='72057594038779904'

得到如下结果:

SQL <wbr>Server数据库存储结构之IAM与数据页

Total_pages,used_pages,data_pages的值发生改变。

为什么插入了一行数据,会增加两页呢?其中一页是数据页,用来存放用户的数据,另外一页叫做IAM(索引分配映射)页,用来将数据页链接起来。连接方式如下图:

 SQL <wbr>Server数据库存储结构之IAM与数据页

 

Sytem_internals_allocation_units表存放第一个数据页和第一个IAM页的指针。IAM按照数据页的顺序存放数据页的指针。数据页之间并无直接链接。

为更进一步说明这种存储结构,我们首先从查询Sytem_internals_allocation_units表:

SELECT total_pages,used_pages,data_pages,

       first_page,root_page,first_iam_page

  FROM sys.system_internals_allocation_units

WHERE container_id ='72057594038779904'

结果如下:

SQL <wbr>Server数据库存储结构之IAM与数据页

 

根据页面大小的规则,定义如下函数来计算页面数:

CREATE FUNCTION [dbo].f_get_page(@page_num BINARY(6))

RETURNS VARCHAR(11)

AS

BEGIN

RETURN(CONVERT(VARCHAR(2),(CONVERT(INT,SUBSTRING(@page_num,6,1))*POWER(2,8))+

        (CONVERT(INT,SUBSTRING(@page_num,5,1))))+':'+

        CONVERT(VARCHAR(11),

        (CONVERT(INT,SUBSTRING(@page_num,4,1))*POWER(2,24))+

        (CONVERT(INT,SUBSTRING(@page_num,3,1))*POWER(2,16))+

        (CONVERT(INT,SUBSTRING(@page_num,2,1))*POWER(2,8))+

        (CONVERT(INT,SUBSTRING(@page_num,1,1)))))

END

使用上述函数计算出数据页是79页,IAM页是80页。

接下来使用DBCC PAGE查询IAM页内容:

Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 90                         m_slotCnt = 2                        m_freeCnt = 6

m_freeData = 8182                    m_reservedCnt = 0                    m_lsn = (20:324:9)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                      

 

Allocation Status

 

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL                  DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED           

 

IAM: Header @0x5950C064 Slot 0, Offset 96

 

sequenceNumber = 0                   status = 0x0                         objectId = 0

indexId = 0                          page_count = 0                       start_pg = (1:0)

 

 

IAM: Single Page Allocations @0x5950C08E

 

Slot 0 = (1:79)                      Slot 1 = (0:0)                       Slot 2 = (0:0)

Slot 3 = (0:0)                       Slot 4 = (0:0)                       Slot 5 = (0:0)

Slot 6 = (0:0)                       Slot 7 = (0:0)                      

 

 

IAM: Extent Alloc Status Slot 1 @0x5950C0C2

 

(1:0)        - (1:376)      = NOT ALLOCATED                              

加亮部分表明了IAM对应的分区信息,以及第一个数据页面指针指向79页。这与我们查询出的first_page值是一致的。一个IAM页面对应8个数据页,当超过8个数据页时,系统会从其对应的4GB空间(约512000个页面)中分配统一区的页面。当数据页超过可分配的页面数时,建立新的IAM页。

接下来查询数据页内容(部分省略):

Slot 0 Offset 0x60 Length 139

 

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Record Size = 139                    

Memory Dump @0x59ECC060

 

00000000:   30007000 01000000 496e526f 77202020 †0.p.....InRow           

00000010:   20202020 20202020 20202020 20202020 †                        

00000020:   20202020 20202020 20202020 20202020 †                         

00000030:   20202020 20202020 20202020 20202020 †                        

00000040:   20202020 20202020 20202020 20202020 †                        

00000050:   20202020 20202020 20202020 20202020 †                        

00000060:   20202020 20202020 20202020 01000000 †            ....        

00000070:   06002002 0082008b 00746573 74496e52 †.. ..‚.‹.testInR        

00000080:   6f77496e 526f7744 617461†††††††††††††owInRowData 

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

 

ID = 1                              

 

Slot 0 Column 2 Offset 0x79 Length 9 Length (physical) 9

 

name = testInRow                    

 

Slot 0 Column 3 Offset 0x8 Length 100 Length (physical) 100

 

type = InRow                                                                                               

 

Slot 0 Column 4 Offset 0x82 Length 9 Length (physical) 9

 

summary = InRowData                 

 

Slot 0 Column 5 Offset 0x6c Length 4 Length (physical) 4

 

price = 1                           

 

Slot 0 Column 6 Offset 0x0 Length 0 Length (physical) 0

 

description = [NULL]                

这个页面里面的数据正是我们刚才插入的数据。

下面我们再插入一行数据:

insert into TestTable values('testInRow1', 'InRow1', 'InRowData1', 1, null)

现在查询Sytem_internals_allocation_units表中页面的分配,页面没有增加。因为没有产生新的数据页。

接下来我们循环插入1000条数据:

declare @i int

set @i=2

while @i<=1000

begin

      insert into TestTable values('testInRow'+CAST(@i as varchar(3)), 'InRow'+CAST(@i as varchar(3)), 'InRowData'+CAST(@i as varchar(3)), @i, null)

      set @i = @i+1

end

再次查询页面分配,结果如下:

SQL <wbr>Server数据库存储结构之IAM与数据页

First_pagefirst_iam_page没有变化,但是数据页数据增加了。

使用DBCC来查看数据页的信息,可以看到该页每一行的数据。

接下来查看IAM的信息,如下:

Slot 0 = (1:79)                      Slot 1 = (1:89)                      Slot 2 = (1:90)

Slot 3 = (1:93)                      Slot 4 = (1:94)                      Slot 5 = (1:109)

Slot 6 = (1:110)                     Slot 7 = (1:114)  

IAM: Extent Alloc Status Slot 1 @0x592EC0C2

 

(1:0)        - (1:176)      = NOT ALLOCATED                              

(1:184)      - (1:192)      =     ALLOCATED                              

(1:200)      - (1:376)      = NOT ALLOCATED 

每一个指针都指向一个数据页。当分配数据页超过8个混合分区后,系统会为数据表分配统一分区。这里,系统为数据表分配184191 192199两个统一分区。每个分区包含8页,加上8个混合分区的页面,一共是25个页面。这25个页面中,使用了19个数据页,加上1IAM页共使用了20个页面。因为统一分区是顺序分配的, 所以可以计算出从195199的页面没有被使用。用DBCC可以验证这个推算。

下面我们把插入的数据删除,然后再查看IAM的页面分配情况,发现页面分配不会因为数据删除而改变,数据页内仅仅是将数据清空而已。

 

PAGE: (1:89)

 

 

BUFFER:

 

 

BUF @0x0438E120

 

bpage = 0x06036000                   bhash = 0x00000000                   bpageno = (1:89)

bdbid = 7                            breferences = 0                      bcputicks = 0

bsampleCount = 0                     bUse1 = 237                          bstat = 0xc00009

blog = 0x21bb7979                    bnext = 0x00000000                  

 

PAGE HEADER:

 

 

Page @0x06036000

 

m_pageId = (1:89)                    m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8208

m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594039828480                                

Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 112                        m_slotCnt = 53                       m_freeCnt = 7990

m_freeData = 7689                    m_reservedCnt = 7593                 m_lsn = (221:265:61)

m_xactReserved = 7593                m_xdesId = (0:92341)                 m_ghostRecCnt = 0

m_tornBits = -1698770727            

 

Allocation Status

 

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED          

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED           

 

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

0

阅读 评论 收藏 转载 喜欢 打印举报
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4006900000 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有