SQL2005中的事务与锁定(九)

 

SQL2005中的事务与锁定(九)

()

------------------------------------------------------------------------

-- Author : HappyFlyStone

-- Date   : 2009-11-09

-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

--      Apr 14 2006 01:12:25

--      Copyright (c) 1988-2005 Microsoft Corporation

--      Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

--       转载请注明出处,更多请关注:http://blog.csdn.net/happyflystone

--       关键字:行版本存储相关知识准备

------------------------------------------------------------------------

   

15、详述行版本存储区

 

    前面说了好多的行版知识,那在这两种隔离等级下,数据有什么表现呢,这一节我们来详细的说说。

    为了更好的分析行版的记录行,先说点相关的知识点。有如下相关的知识我们来学习行版会轻松点。

A、 如何查看数据页面(DBCC PAGEDBCC TRACEON

我们可以通过DBCC来查看数据页面内容,这个命令可以看到数据库中的页面报头、数据行及行偏移表。虽然这个命令只是系统管理员才可以执行,但是一般操作人员也不会去看页面内容。

 

DBCC PAGE的命令格式如下:

DBCC PAGE({dbid|dbname},filenum,pagenum[,printopt])

Dbid|dbname  数据库ID或库名

Filenum       页面的文件号

Pagenum       指定文件内的页面号

Printopt      输出选项:0-默认值,缓冲报头及页面报头

             1-对每记录行分别输出缓冲及页面报头,行偏离表

             2-整体的缓冲、页面报头及行偏离表

             3-完整的报头、行偏离表并可看到行中的各列值

 

DBCC TRACEON格式:

DBCC TRACEON(3604)

必须先打开跟踪3604来让DBCC PAGE的结果输出给客户端。

 

我们来看看一个DBCC PAGE的结果(TA只二条记录)

 

PAGE: (1:89)

BUFFER:

BUF @0x02BFFDF0

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

bdbid = 8                            breferences = 0                      bUse1 = 23490

bstat = 0xc00009                     blog = 0x21432159                    bnext = 0x00000000

PAGE HEADER:

Page @0x04938000

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

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xa200

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

Metadata: AllocUnitId = 72057594043564032                                

Metadata: PartitionId = 72057594038583296                        Metadata: IndexId = 0

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

pminlen = 18                         m_slotCnt = 2                        m_freeCnt = 8022

m_freeData = 166                     m_reservedCnt = 0                    m_lsn = (45:288:2)

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

m_tornBits = -1441945315            

Allocation Status

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

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

ML (1:7) = NOT MIN_LOGGED           

DATA:

Slot 0, Offset 0x60, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x3432C060

00000000:   50001200 01000000 61616161 61616161 †P.......aaaaaaaa        

00000010:   61610200 fc000000 00000000 000c0000 †aa..............        

00000020:   000000†††††††††††††††††††††††††††††††... 

--删除了一条记录信息                   

OFFSET TABLE:

Row - Offset                        

1 (0x1) - 131 (0x83)                

0 (0x0) - 96 (0x60)   

Buffer:当前页面调入内存时,要为了便于管理内存中这个页面生成的一种结构。

Page Head:报头。(部分解释如下)

m_pageId当前页面的文件号及页面号。

m_level当前页面在索引中的级数。

AllocUnitId 分配单元ID

PartitionId 分区ID

ObjectId所属对象的ID

IndexId页面的索引ID

m_prevPage前一页面指针,

m_nextPage下一页面指针。

Pminlen 行定长部分字节数。

m_freeData页面第一个空闲字节偏移量。

m_slotCnt总记录数。

m_freeCnt 页面空闲字节数。

DATA:记录每一个行的信息。

                     Slot 0 行号

Offset 0x60 行在页面的偏移量

Length 35 记录长度,

Record Type 记录类型

Record Attributes 属性描述 VERSIONING_INFO行版

              OFFSET TABLE:行偏移矩阵内容

                     1 (0x1) – 131 (0x83)  

0 (0x0) – 96 (0x60)

B、 数据页面

数据页面显然也是一种结构,它其中包含了表中用户数据,它固定为8K大小(8192byte)。一共有三种类型的页面(行内数据IN_ROW_DATA,行溢出数据ROW_OVERFLOW_DATA,大数据LOB页面LOB_DATA),而每一个页面是有三部分级成(报头,数据行,行偏移矩阵),这三部分我们在刚才上面的报告输出有所体现。 页面报头总是占用页面的前96个字节,所以我们页面只有8096个字节用于数据行及行偏移存储,这个以后估算表大小时一定要注意。

96个字节后的区域是真正的行数据部分,对于行内数据来说一个行的最大为8060个字节。对于行固定的表来说,每一个页面上存储的记录数总是一定的,如果变长时要根据输入的数据而定。从减少IO及缓存命中率来说,我们要尽量使得页面容纳尽量多的记录。

行偏移矩阵是2个字节的块,以两个字节为单位,每一个行就有两个字节的块,矩阵体现了记录在页面上的逻辑顺序,在这儿得注意一下,有聚集索引的表,SQLSERVER按索引键值来存储,不是意思着页面上的物理存储也一定是键值的顺序哦,实际上这个顺序是由矩阵的逻辑顺序来保证的,下面我做一个测试 给大家看看:

 

 

create table ta(

id int primary key ,

col3 char(1))

insert into ta select  1,'a'

insert into ta select  2,'a'

insert into ta select  3,'a'

insert into ta select  4,'a'

insert into ta select  5,'a'

insert into ta select  6,'a'

insert into ta select  11,'a'

insert into ta select  21,'a'

insert into ta select  31,'a'

insert into ta select  41,'a'

insert into ta select  51,'a'

insert into ta select  61,'a'

insert into ta select 10 ,'b'

 

go

dbcc ind(testcsdn,'ta',-1) –227

go

dbcc traceon(3604)

go

dbcc page('testcsdn',1,227,1)

go

/*

DATA:

 

Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Memory Dump @0x443CC060

00000000:   10000900 01000000 610200fc ††††††††††........a...           

。。。。。。--省略了中间的行记录

 

00000000:   10000900 06000000 610200fc ††††††††††........a...            

 

Slot 6, Offset 0xf0, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP    

Memory Dump @0x443CC0F0

00000000:   10000900 0a000000 620200fc ††††††††††........b...            

 

Slot 7, Offset 0xa8, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP    

Memory Dump @0x443CC0A8

00000000:   10000900 0b000000 610200fc ††††††††††........a...             

。。。。。。--省略

OFFSET TABLE:

Row - Offset                        

12 (0xc) - 228 (0xe4)               

11 (0xb) - 216 (0xd8)               

10 (0xa) - 204 (0xcc)               

9 (0x9) - 192 (0xc0)                

8 (0x8) - 180 (0xb4)                

7 (0x7) - 168 (0xa8)                

6 (0x6) - 240 (0xf0)                

5 (0x5) - 156 (0x9c)                

4 (0x4) - 144 (0x90)                

3 (0x3) - 132 (0x84)                

2 (0x2) - 120 (0x78)                

1 (0x1) - 108 (0x6c)                 

0 (0x0) - 96 (0x60) 

*/    

注意报表的结果的红色部分。

C、 数据行结构

上面的页面结构里我们已经看到数据行的结构,只是我们没有对行的数据做介绍,下面就是一个行的数据,我们来详细说说行的结构。

insert into ta select 10 ,'b'

 

Slot 6, Offset 0xf0, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP    

Memory Dump @0x443CC0F0

00000000:   10000900 0a000000 620200fc ††††††††††........b... 

在这儿我们只讨论固定长度列的行结构,变长列的行结构不讨论。

我们先说行的长度,我们假设固定列类型基本长度和为N,固定列的列数为M,那么长度的基本公式为:N+ceiling(M/8)+6。上面的报告报头我可以看得出长度是12,那用这个公式来试试:5+ceiling( 2/8) + 6 = 12 .

好,行的结构究竟是什么样呢?(仅考虑固定列长)

B状态1 + B状态2 + W列偏移 + L数据 + W列数 + NULL状态位

B       - 1 个字节

W       - 2 个字节

L       - 变长

NULL状态位  - Ceiling(W列数/8)

那好,我们根据上面的行结构来解释一下slot 6记录,第一个字节0x10 ,第四个bit位是1 表示存在NULL状态位,第5Bit位是表示有无变长列,0表示行无变长列(其它bit位不介绍了)。第二个字节是状态2,暂未使用。第三、四字节对调为0x0009表示列位移量,那对于这个行记录列偏移量怎么是9的呢? 好,我们看列数据真正是从第5个字节开始的,一个整形(4个字节)+一个长度为1的字符(1个字节)共5个字节,54 = 9 , 所以得到列当前的偏移为9。第1011个字节对调后是0x0002表示有两个列,第12个字节表示的NULL状态位,这个正好和第一个字节的第四个bit位对应上,一个Bit表示一个列的NULL特性,Ceiling(2/8) = 1,当前值是0xfc,11111100B),BIT位表示是否为NULL从高到低的顺序显示,本例中只有两列最后全是0 表示都不为空(1代表当前对应的列为NULL)。

根据上面的说明大家可以自己试试。 

D、 如何得到页面号(pagenum)

上面说了这么多我们有一个关键的东东没说,什么呢,那就是如何得到当前表的页面号呢?好,那我们在这儿可以介绍两种办法:

1、 DBCC IND()

 

DBCC IND
(
 

['dbname'|dbid]-- 数据库名或ID
  tbname, -- 表名

Printoptnoclustered index_id [, --输出选项

Partition_num] –-指定分区号,主要兼容2000

)

Printopt : --输出选项(常用的)

-- noclustered index_id   所有IAM、数据及指定索引的分页信息  

-- -2   所有IAM页面

-- -1   所有的数据、索引、IAM、行溢出及LOB页面

--  0   行内数据、行内数据的IAM页面

--  1   聚集索引及所有数据、IAMLOB页面

Partition_num

--主要是兼容2000,缺省0为所有分区,在2005里我们可以指定特定的分区号

 

 

2、  system_internals_allocation_units取到first_page

 

---------------------------------------------------------------------

--            查询表的文件号及页面号

-- Author : HappyFlyStone

-- Date   : 2009-11-19 13:23:02

-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

--      Apr 14 2006 01:12:25

--      Copyright (c) 1988-2005 Microsoft Corporation

--      Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

--   blog : http://blog.csdn.net/happyflystone          

--      转载注明出处及相关信息

---------------------------------------------------------------------

 

;WITH T1

AS

(

    SELECT OBJECT_NAME(OBJECT_ID) AS NAME,TYPE_DESC,FIRST_PAGE

    FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS U

    JOIN SYS.PARTITIONS P

    ON U.CONTAINER_ID = P.PARTITION_ID

    WHERE [OBJECT_ID] = OBJECT_ID('TA')

)

SELECT *,CAST(

           CONVERT(INT,SUBSTRING(FIRST_PAGE,6,1)) * POWER(2,8)

         + CONVERT(INT,SUBSTRING(FIRST_PAGE,5,1))

           AS VARCHAR)+':'+CAST(

          (CONVERT(INT,SUBSTRING(FIRST_PAGE,4,1)) * POWER(2,24))

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

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

         + (CONVERT(INT,SUBSTRING(FIRST_PAGE,1,1))) AS VARCHAR) AS 'FILE:PAGE_NUM'

         

FROM T1

 

/*

name    type_desc      first_page     File:page_num

----- --------------- -------------- ----------------

ta     IN_ROW_DATA    0x590000000100     1:89

 

(1 行受影响)

 

*/

 

       对于上述CTE大家可以封装成函数,在需要时调用。

 

3、 DBCC IND输出的相关列名介绍

 

 

  • PageFID 页面的文件号
  • PagePID 页面号
  • IAMFID   IAM所在文件号,如果是IAM页面此项为空
  • IAMPID   对应的IAM页面号,对于IAMNULL
  • ObjectID - 页面所属对象ID
  • IndexID – 索引的ID
  • PartitionNumber – 分区号
  • PartitionID – 分区ID
  • iam_chain_type – 2005有如下几种类型:

1.         IN_ROW_DATA  

2.         LOB_DATA 

3.       ROW_OVERFLOW_DATA  

  • PageType – 页面类型

1 - data page

2 - index page

3 and 4 - text pages

8 - GAM page

9 - SGAM page

10 - IAM page

11 - PFS page

  • IndexLevel – 这个对应页面报头部分的m_levell当前页面在索引中的级数

 

 

请大家继续关注我的blog: http://blog.csdn.net/happyflystone

 

 

 

展开阅读全文
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值