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 PAGE、DBCC 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状态位，第5个Bit位是表示有无变长列，0表示行无变长列（其它bit位不介绍了）。第二个字节是状态2,暂未使用。第三、四字节对调为0x0009表示列位移量，那对于这个行记录列偏移量怎么是9的呢？ 好，我们看列数据真正是从第5个字节开始的，一个整形（4个字节）＋一个长度为1的字符（1个字节）共5个字节，5＋4 ＝ 9 ， 所以得到列当前的偏移为9。第10、11个字节对调后是0x0002表示有两个列，第12个字节表示的NULL状态位，这个正好和第一个字节的第四个bit位对应上，一个Bit表示一个列的NULL特性，Ceiling(2数/8) = 1，当前值是0xfc,即11111100（B），BIT位表示是否为NULL从高到低的顺序显示，本例中只有两列最后全是0 表示都不为空（1代表当前对应的列为NULL）。
根据上面的说明大家可以自己试试。
D、如何得到页面号(pagenum)
上面说了这么多我们有一个关键的东东没说，什么呢，那就是如何得到当前表的页面号呢？好，那我们在这儿可以介绍两种办法：
1、DBCC IND()
DBCC IND
(
['dbname'|dbid], -- 数据库名或ID
tbname, -- 表名
Printopt｜noclustered index_id [, --输出选项
Partition_num] –-指定分区号，主要兼容2000
)
Printopt : --输出选项（常用的）
-- noclustered index_id 所有IAM、数据及指定索引的分页信息
-- -2 所有IAM页面
-- -1 所有的数据、索引、IAM、行溢出及LOB页面
-- 0 行内数据、行内数据的IAM页面
-- 1 聚集索引及所有数据、IAM、LOB页面
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页面号，对于IAM为NULL
?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_level，l当前页面在索引中的级数
请大家继续关注我的blog: http://blog.csdn.net/happyflystone
[解决办法]
SF
[解决办法]
