- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

sql server 数据修改的内部原理

eric0435 发表于 2022/02/25 08:25:18 2022/02/25

【摘要】当插入新的记录到数据表中时，SQL Server必须决定在哪里插入记录。如果数据表没有聚集索引—也就是说，数据表还是堆表的话–新的记录总是插到表中任何有可用空间的地方。如果空间中没有可用的分页，SQL Server会尝试从已经分配给对象的现有的统一扩展中寻找未分配的分页。如果这样的分页也不存在，SQL Server就必须为数据表分配一个全新的扩展。聚集索引根据新记录的聚集索引键列的值将插入导...

当插入新的记录到数据表中时，SQL Server必须决定在哪里插入记录。如果数据表没有聚集索引—也就是说，数据表还是堆表的话–新的记录总是插到表中任何有可用空间的地方。如果空间中没有可用的分页，SQL Server会尝试从已经分配给对象的现有的统一扩展中寻找未分配的分页。如果这样的分页也不存在，SQL Server就必须为数据表分配一个全新的扩展。

聚集索引根据新记录的聚集索引键列的值将插入导向到特定的分页。当新的记录是插入语句的直接结果或者是由“删除+插入”策略所执行的更新语句的结果时就会发生插入。SQL Server将新的记录插入到聚集索引中的合适位置，如果当前分页没有可用空间的话，会通过页拆分(page split)在分页中进行接合。如果不将聚集索引声明为唯一性并插入具有重复键值的记录时，SQL Server会为所有后续的具有相同键值的记录自动生成一个唯一标识符(uniqueifier)。于是，SQL Server在内部将所有的聚集索引键都看作是唯一的。

页拆分(Splitting Pages)
SQL Server找到新的可用分页以后，原来的分页就要被拆分：行的一半留在原来的分页上，而另一半则被移到新的分页上。在某些情况下，由于变长字段的长度可能会变得远远大于分页上任何现有的记录，SQL Server会发现即使在页拆分以后仍然没有可用空间来存放新的记录。拆分后，一条或多条记录会被提升到父级分页中。如果只需要一次拆分，那么就提升一条记录。然而，如果在一次拆分后仍然无法容纳新的记录，那么就可能会潜在地产生多个新的分页并产生多次到父级分页的提升。举例来说，比如一个具有32行数据的分页。假设SQL Server尝试插入一条8000个字节长的新记录。SQL Server会对该分页进行一次拆分，但是无法丙容纳一条800个字节长的新记录。即使在第二次拆分后，仍然无法容纳新的记录。最终，SQL Server会意识到如果分页中有任何其他记录存在的话是无法容纳这条新记录的，因此它会分配一个新的分页来容纳这条唯一的新记录。于是，会发生几次页拆分，产生许多新的分页以及父级分页中的许多新的行。

SQL Server总是从根节点向下搜索索引树的，因此在插入操作的过程中，分页是在下层中进行的。这意味着当插入操作搜索索引的时候，需要将索引保护起来防止可能的更新操作。这种保护机制使用闩锁。当分页被读取或者被写入磁盘的时候，SQL Server会获取闩锁来保护分页内容的物理完整性。父节点(非叶节点)被闩锁锁住直到得知子节点拥有自己的闩锁来保护时为止。然后，父级的闩锁就可以安全地被释放掉了。

在父级节点上的闩锁被释放以前，SQL Server会判断分页是否能够容纳另两行数据。如果不能的话，就进行页拆分。最终目的是确保父级分页总是有足够的空间容纳记录或者由子分页拆分所得到的记录(有时候这会导致无需进行页拆分–至少尚未拆分。这将带来性能上的优化）。拆分的类型取决于被拆分分页的类型：索引的根分页，中间级的索引分页以及数据分页。

拆分索引的根分页
如果索引的根分页需要为插入一条新的索引也被拆分的话，SQL Server会为此索引分配两个新的分页。根分页中的所有记录在这两个新分页中被拆分，而新的索引行也被插入到这些分页之中的某个合适的位置。原来的根分页仍然是根，但是现在上面只有两条记录，指向新分配的两个分页。根的页拆分会在索引中创建一个新的级别。由于索引通常只有几层的深度，这种类型的拆分并不会经常发生。

拆分中间级索引分页
中间级索引分页的拆分是通过定位分页上索引键的中点，分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。同样地，尽管这比根分页的拆分要常见一些，但这种情况也并不会经常发生。

拆分数据分页
数据分页的拆分是最值得关注也是最可能发生的情况，并且这也可能是开发者唯一应该关注的拆分类型。数据分页的拆分只有在表上存在聚集索引且进行插入操作时才会发生。如果不存在聚集索引，SQL Server会根据PFS分页在任何可以容纳新记录的分页上完成插入。尽管只有插入行为才会引起拆分，但这种插入行为也可能是update语句产生的结果，而不仅仅针对insert语句。正如下面将要介绍的，如果行不能在原处被更新或者至少在同一个分页中被更新的话，更新操作实际上就被分解为先删除原始行再插入行的一个新版本。当然，插入新的记录还会造成页拆分。

拆分数据分页是一个复杂的操作。与拆分中间级索引分页非常类似的是，SQL Server也是通过定位分页上的索引键的中点，分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。这需要索引管理器决定在哪个分页上放置新的记录并且能够处理原业的旧分页和新分页都不能容纳的大的行记录。当数据分页被拆分时，聚集索引键值不会改变，因而非聚集索引也不会受到影响。

下面来看看当发生拆分时分页上的情况。下面的脚本新建了一个具有较大行记录的数据表bigrows—相当大，实际上一个分页上只能容纳五行数据。一旦表被创建并插入五行记录以后，我们可以通过如下步骤找到该表的第一个分页(在本例中也是唯一的分页):将dbcc ind的输出结果插入到sp_table_pages表中，寻找没有前一页的数据分页的信息然后使用dbcc page来查看分页的内容。由于没有必要查看分页上所有的8020个字节的数据，我们只需要查看分页末尾的行偏移数组并观察当插入第六条记录时分页上的情况。

/*首先创建数据表bigrows */
use adventureworks;
GO

drop table bigrows;
GO

create table bigrows
(
a int primary key,
b varchar(1600)
);
GO

/* 向表中插入五行数据 */
insert into bigrows values(5,replicate(‘a’,1600));
insert into bigrows values(10,replicate(‘b’,1600));
insert into bigrows values(15,replicate(‘c’,1600));
insert into bigrows values(20,replicate(‘d’,1600));
insert into bigrows values(25,replicate(‘e’,1600));
GO

truncate table sp_table_pages;
insert into sp_table_pages exec(‘dbcc ind(adventureworks,bigrows,-1)’);
select pagefid,pagepid from sp_table_pages where pagetype=1;

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

(2 行受影响)
pagefid pagepid

1 20996

(1 行受影响)

dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20996,1);

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:20996)

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 686
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 5 m_freeCnt = 11
m_freeData = 8171 m_reservedCnt = 0 m_lsn = (134:408:2)
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:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22C060

0000000000000000: 30000800 05000000 0200fc01 004f0661 ?0…O.a
0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000020: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000030: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000600: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
…省略…

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22C6AF

Slot 2, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22CCFE

0000000000000000: 30000800 0f000000 0200fc01 004f0663 ?0…O.c
0000000000000010: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
…省略…

Slot 3, Offset 0x134d, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22D34D

0000000000000000: 30000800 14000000 0200fc01 004f0664 ?0…O.d
0000000000000010: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000020: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
0000000000000030: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
…省略…

Slot 4, Offset 0x199c, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E22D99C

0000000000000000: 30000800 19000000 0200fc01 004f0665 ?0…O.e
0000000000000010: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000020: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000030: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
0000000000000040: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee
…省略…

下面是从dbcc page输出结果中得到的行偏移数组:

OFFSET TABLE:

Row - Offset
4 (0x4) - 6556 (0x199c)
3 (0x3) - 4941 (0x134d)
2 (0x2) - 3326 (0xcfe)
1 (0x1) - 1711 (0x6af)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

下面插入一行新的记录并再次查看行偏移数组:

use adventureworks;
GO
insert into bigrows values(22,replicate(‘x’,1600));
GO

从m_nextPage = (1:20999)结果可知现在有两个数据分页，下一个分页号为20999。

PAGE: (1:20996)

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 4322
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (0:0) m_nextPage = (1:20999)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 3245
m_freeData = 8171 m_reservedCnt = 0 m_lsn = (134:416:17)
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:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C060

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C6AF

0000000000000000: 30000800 0a000000 0200fc01 004f0662 ?0…O.b
0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000020: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000030: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000040: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
…省略…

Slot 2, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8CCFE

OFFSET TABLE:

Row - Offset
2 (0x2) - 3326 (0xcfe)
1 (0x1) - 1711 (0x6af)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

在拆分后检查原来的数据分页发现它既有可能包含原始分页的上半部分的行记录也可能是下半部分。SQL Server通常会移动记录因而新插入的记录会移动到新的分页上去。因为这些行记录无论如何都会移动，因此调整它们的位置来容纳新插入的记录就更具有意义了。在这个例子中，新的记录，其聚集键值为22，会被插入到分页的下半部分中去。因此当发生页拆分时，前三行仍然留在原始分页20996中。可以检查页头来找到下一个包含新记录的分页位置。其中,m_nextPage域指明了页号。该值以十进制的格式表示为文件号；分页号对，因此可以很容易地将其运用在dbcc page命令中。当我运行该查询时，得到的m_nextPage的值为1:20999，因此要执行如下命令:

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20999,1)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:20999)

BUFFER:

BUF @0x0000000083FF9C80

bpage = 0x0000000083E72000 bhash = 0x0000000000000000 bpageno = (1:20999)
bdbid = 6 breferences = 0 bUse1 = 7865
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E72000

m_pageId = (1:20999) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 346 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060603392
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 1
Metadata: ObjectId = 743673697 m_prevPage = (1:20996) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 3245
m_freeData = 4941 m_reservedCnt = 0 m_lsn = (134:416:20)
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:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C060

0000000000000000: 30000800 14000000 0200fc01 004f0664 ?0…O.d
0000000000000010: 64646464 64646464 64646464 64646464 ?dddddddddddddddd
…省略…

Slot 1, Offset 0xcfe, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8CCFE

0000000000000000: 30000800 16000000 0200fc01 004f0678 ?0…O.x
0000000000000010: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
0000000000000020: 78787878 78787878 78787878 78787878 ?xxxxxxxxxxxxxxxx
…省略…

Slot 2, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000003A8C6AF

OFFSET TABLE:

下面是插入以后第二个分页上的行偏移数组:

Row - Offset
2 (0x2) - 1711 (0x6af)
1 (0x1) - 3326 (0xcfe)
0 (0x0) - 96 (0x60)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

注意，在页拆分以后，此分页上有三行记录:原始分页上的后两条记录(键值为20和25)，以及值为22的新记录。如果检查分页上的实际数据的话，会注意到新的记录在槽号(slot)为1的地方，即使该记录本身的确是分页上物理顺序中的最后条记录。槽1(键值为22)从偏移量3326处开始，而槽2(键值为25)则从偏移量1711处开始。行的聚集索引键序是由行的槽号指明的，而不是在分页上的物理位置。如果数据表包含聚集索引，那么在槽1处的记录的键值总是小于槽2处记录的键值而大于槽0处记录的键值。

尽管典型的页拆分的代价并不十分高昂，但是用户还是希望能够尽可能降低生产系统中的页拆分频率，至少在使用高峰的时刻会如此。一次页拆分开锁很低，但成千上百的拆分就不是了。可以通过在现有数据上创建聚集索引时使用filefactor子句在分页上预留一些空间来避免高峰时刻可能的系统崩溃。可以在系统运营最不繁忙的时雄姿英发定期使用期望的填充因子重建索引对系统进行优化。按照这种方式，在高峰时刻依然有额外的空间可以使用，并且可以因此节省拆分带来的开锁。如果系统不存在“不活跃”的时间，可以使用alter index对索引进行重组并调整填充因子，而不必使整张表都陷入不可使用的状态。注意：带有reorganize的alter index语句只能通过压缩数据和转移分页来调整填充因子，它不会通过添加新的分页来重置填充因子。利用SQL Server代理可以很轻松地在系统不繁忙时安排索引的重建或重组。

删除数据
当从一张数据表中删除记录时，必须同时考虑数据分页和索引分页上的变化。谨记聚集索引的叶级别实际上就是数据本身，而从带有聚集索引的表中删除数据的方式与从非聚集索引的叶级别删除记录完全一样。从堆表中删除记录则是另一种处理方式，类似于从索引的结点分页中进行删除。

从堆表中删除记录
SQL Server 2005不会在行被删除时自动压缩分页上的空间。考虑到性能优化，在分页需要额外的连续空间来插入新的记录以前都不会进行数据压缩。下面的盒子从分页中间删除一行数据然后使用dbcc page来查看分页。

use adventureworks;
GO

create table smallrows
(
a int identity,
b char(10)
);
GO

insert into smallrows values(‘row 1’);
insert into smallrows values(‘row 2’);
insert into smallrows values(‘row 3’);
insert into smallrows values(‘row 4’);
insert into smallrows values(‘row 5’);

truncate table sp_table_pages;

insert into sp_table_pages exec(‘dbcc ind(adventureworks,smallrows,-1)’);

select pagefid,pagepid from sp_table_pages where pagetype=1;
结果:
pagefid pagepid

1 21920

(1 行受影响)

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21920,1)

下面是dbcc page的输出结果

PAGE: (1:21920)

BUFFER:

BUF @0x0000000083FF9480

bpage = 0x0000000083E52000 bhash = 0x0000000000000000 bpageno = (1:21920)
bdbid = 6 breferences = 0 bUse1 = 3478
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E52000

m_pageId = (1:21920) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 347 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060668928
Metadata: PartitionId = 72057594054377472 Metadata: IndexId = 0
Metadata: ObjectId = 775673811 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (134:472:3)
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:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

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

0000000000000000: 10001200 01000000 726f7720 31202020 ?..row 1
0000000000000010: 20200200 fc??? …

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 02000000 726f7720 32202020 ?..row 2
0000000000000010: 20200200 fc??? …

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 03000000 726f7720 33202020 ?..row 3
0000000000000010: 20200200 fc??? …

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 04000000 726f7720 34202020 ?..row 4
0000000000000010: 20200200 fc??? …

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 05000000 726f7720 35202020 ?..row 5
0000000000000010: 20200200 fc??? …

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

现在删除中间一行数据(where a=3)并再次查看分页的内容

use adventureworks;
GO
delete from smallrows where a=3;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21920,1)

下面是第二次执行dbcc page的输出结果

PAGE: (1:21920)

BUFFER:

BUF @0x0000000083FF9480

bpage = 0x0000000083E52000 bhash = 0x0000000000000000 bpageno = (1:21920)
bdbid = 6 breferences = 3 bUse1 = 3693
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000083E52000

m_pageId = (1:21920) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 347 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060668928
Metadata: PartitionId = 72057594054377472 Metadata: IndexId = 0
Metadata: ObjectId = 775673811 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8002
m_freeData = 201 m_reservedCnt = 21 m_lsn = (134:488:2)
m_xactReserved = 21 m_xdesId = (0:18192) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

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

0000000000000000: 10001200 01000000 726f7720 31202020 ?..row 1
0000000000000010: 20200200 fc??? …

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 02000000 726f7720 32202020 ?..row 2
0000000000000010: 20200200 fc??? …

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 04000000 726f7720 34202020 ?..row 4
0000000000000010: 20200200 fc??? …

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 05000000 726f7720 35202020 ?..row 5
0000000000000010: 20200200 fc??? …

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 0 (0x0)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

注意在堆表中，这行数据就不会出现在分页中了。分页底部的行偏移数组显示第三行数据(槽2)现在位于偏移量0处(这意味着现在没有行使用槽2)，而占用槽3的行仍然在位于和删除前相同的偏移量处。分页上的数据并没有被压缩。

除了分页上的空间不被回收之外，堆表中的空闲分页也经常无法被回收。即使从堆表中删除了全部记录。SQL Server不会将空闲分页标记成未分配状态，因此这部分空间无法供其他对象使用。目录视图sys.dm_db_partition_stats会显示这块空间仍然属于堆表。

从B树中删除记录
在索引的叶级别(无论聚集索引还是非聚集索引)，当行记录被删除时，SQL Server会将其标记为虚影记录。这意味着这行记录仍然保留在分页上但是SQL Server会修改记录头中的一个比特位用来表示这行数据实际上是一个虚影。页头中也会反映出分页上的虚影记录数量。虚影记录有多种用途。它们可以使用回滚操作具有更高的效率；如果这行记录还没有被物理地移除掉，那么SQL Server要回滚删除操作的话就只需修改表示行为虚影的那个比特位即可。同样，虚影记录也是一种针对键范围锁定以及其他锁定模式的并发优化。虚影记录也被用来支持行版本控制。

虚影记录迟早还是会被清除掉的，取决于系统的负荷情况，而且有时SQL Server会在你有机会查看到以前就将其清除掉了。在下面所示的代码中，如果执行delete操作并等待一会儿再运行dbcc page的话，虚影记录可能就真的消失了。这就是为什么要在运行delete以前察看表中的分页数量，这样就可以在查询窗口中一次点击执行delete和dbcc page命令。为了确保虚影不被清除掉，可以将delete放到一个用户事务中并且在检查分页之前不提交或回滚该事务。清理线程不会清除属于活动事务的虚影记录。另外，可以使用未记载的跟踪标记661禁用虚影清理以确保像这个脚本一样运行浿时能够有一致的结果。通常，请记住未被记载的跟踪标记并不能保证在任何后续版本或者service pack中继续工作，并且不被微软支持。还要明确在完成测试以后必须关闭跟踪标记。

下面的例子新建了与前面delete例子中相同的数据表，但这次声明了一个主键，这意味着SQL Server会创建一个聚集索引。聚集索引的叶级别就是实际的数据，因此当记录被移除时，会被标记为虚影。

use adventureworks;
GO

drop table smallrows;
GO

create table smallrows
(
a int identity primary key,
b char(10)
);
GO

truncate table sp_table_pages;

insert into sp_table_pages exec(‘dbcc ind(adventureworks,smallrows,-1)’);

select pagefid,pagepid from sp_table_pages where pagetype=1;
结果:
pagefid pagepid

1 21928

use adventureworks;
GO
delete from smallrows where a=3;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,21928,1)

下面是dbcc page的输出结果:

PAGE: (1:21928)

BUFFER:

BUF @0x0000000084FF6000

bpage = 0x0000000084D80000 bhash = 0x0000000000000000 bpageno = (1:21928)
bdbid = 6 breferences = 3 bUse1 = 5368
bstat = 0xc0010b blog = 0x212121bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000084D80000

m_pageId = (1:21928) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 348 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060734464
Metadata: PartitionId = 72057594054443008 Metadata: IndexId = 1
Metadata: ObjectId = 791673868 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (134:576:2)
m_xactReserved = 0 m_xdesId = (0:18204) m_ghostRecCnt = 1
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

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

0000000000000000: 10001200 01000000 726f7720 31202020 ?..row 1
0000000000000010: 20200200 fc??? …

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 02000000 726f7720 32202020 ?..row 2
0000000000000010: 20200200 fc??? …

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x000000000E22C08A

0000000000000000: 1c001200 03000000 726f7720 33202020 ?..row 3
0000000000000010: 20200200 fc??? …

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 04000000 726f7720 34202020 ?..row 4
0000000000000010: 20200200 fc??? …

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

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

0000000000000000: 10001200 05000000 726f7720 35202020 ?..row 5
0000000000000010: 20200200 fc??? …

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

注意，这行记录仍然会出现在分页中，因为数据表带有聚集索引。行的头信息表明这的确是一条虚影记录。分页末尾的行偏移数组显示槽2中的记录仍然在相同的偏移量处而且所有记录都和删除以前所在的位置相同。另外，页头还提供了分页中虚影记录的数量(m_ghostRecCnt = 1)。可以通过sys.dm_db_index_physical_stats看到数据表中虚影记录的总数

删除索引结点级中的记录
当从表中删除一行数据时，SQL Server必须维护所有的非聚集索引，因为每个非聚集索引都有一个指针指向现在消失了的那条记录。索引结点分页中的记录在被删除以后并不是虚影记录，但是与堆表分页一样，直到新的索引行需要该分页中的空间时，SQL Server才会压缩空间。

回收分页
当数据分页中的最后一条记录都被删除时，整个分页就被回收。例外发生在表是堆表结构的时候，正如之前讨论过的(如果此分页是表中剩下的唯一分页的话是不会被回收的。一张数据表总是包含至少一个分页，即使该分页是空的）。数据分页的回收将导致SQL Server删除索引分页中指向被回收的数据分页的那行记录。如果索引行被删除的话，那么索引分页就会被回收(同样，可能在删除/插入/更新策略时发生)，只在索引分页中保留一个条目。该条目会被移到相邻的分页中，然后SQL Server将回收这个空闲分页。

到目前为止，所有的讨论都集中在删除一行记录所必需的分页操作上。如果一个删除操作删除掉多行记录，用户必须注意一些其他事项。由于在单个查询中修改多条记录对于插入，更新以及删除操作来说都是相同的。

更新行
SQL Server有多种方式可以更新行记录，它会自动隐式地为特定的操作选择最快速的更新策略。在决定策略的过程中，SQL Server会估计受到影响的记录数，按照怎样的方式访问记录（通过扫描，或者一次索引和索引获取）以及是否会对索引键进行修改。更新可以由查询处理器或者存储引擎来控制。在本小节中，我们只研究更新究竟按照原位替换的方式进行还是SQL Server将其按照两个独立的操作来处理；删除旧行并插入新行。更新是由哪个组件控制的问题；查询处理器还是存储引擎，实际上与所有数据修改操作都有关联（并不只与更新相关）。

移动行
如果表中的一条记录必须移到新的位置时又会怎么样呢？在SQL Server 2005中，当一条带有变长字段的记录被更新了一个新的较大的值，使得原始分页无法再容纳此记录时，会发生移动行的情况。这也可能在改变聚集索引的字段时发生，因为数据行是根据聚集键进行逻辑排序的。举例来说，如果在lastname字段上有一个聚集索引，那么一条lastname值为Abbot的记录会存储到接近表头的位置。如果接着将lastname值更新为Zappa，那么就必须将这行数据移到靠近表尾的地方。

前面部分曾经了解了索引的结构并知道非聚集索引的叶级别的每个结点都包含一个行定位符（或者书签）指向数据表中的每行记录。如果表上有聚集索引，那么行定位符就是该行数据的聚集键。因此，当且仅当聚集索引键被更新时，才需要在每个非聚集索引中进行相应的修改。请在决定要在哪些字段上创建聚集索引的时候牢记这点。最好在非易失性的字段上创建聚集索引。

如果行的移动是因为原来的分页不能再容纳这行数据。它依然会保持相同的行定位符（换而言之，该行数据的聚集键保持不变），并且没有非聚集索引需要被修改。

在索引内部机理的讨论中也了解了如果数据表上没有聚集索引（换而言之，仍然是堆表结构），那么存在非聚集索引中的行定位符实际上就是行的物理位置。在SQL Server 2005中，如果堆中的一行记录要移到新的分页上去，该记录会在原来的位置留下一个“前转指针”(forwarding pointer)。SQL Server无需改变非聚集索引，它们依旧指向原来的位置，并从原位出发指向新的位置。

下面来看一个例子。首先新建一张表，与之前演示插入操作的那张表十分相似，但是该表还有第三个变长字段。当向这张表插入五条数据以后，会填满这个分页，然后更新其中一行记录使第三个字段变得相当长。原始分页就无法再容纳这行数据，因此必须被移动。接着可以将dbcc ind的输出结果导入sp_table_pages数据表中，以得到该表所使用的分页数目。

use adventureworks;
GO

drop table bigrows;
GO

create table bigrows
(
a int identity,
b varchar(1600),
c varchar(1600)
);
GO

insert into bigrows values(replicate(‘a’,1600),’’);
insert into bigrows values(replicate(‘b’,1600),’’);
insert into bigrows values(replicate(‘c’,1600),’’);
insert into bigrows values(replicate(‘d’,1600),’’);
insert into bigrows values(replicate(‘e’,1600),’’);
GO

update bigrows set c=replicate(‘x’,1600) where a=3;
GO

truncate table sp_table_pages;

insert into sp_table_pages exec(‘dbcc ind(adventureworks,bigrows,-1)’);

select pagefid,pagepid from sp_table_pages where pagetype=1;

查看输出结果:

pagefid pagepid

1 20996
1 20997

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20996,1)
PAGE: (1:20996)

输出结果如下:

BUFFER:

BUF @0x0000000083FD7F80

bpage = 0x00000000835FE000 bhash = 0x0000000000000000 bpageno = (1:20996)
bdbid = 6 breferences = 0 bUse1 = 42333
bstat = 0xc0010b blog = 0x2121bbbb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000835FE000

m_pageId = (1:20996) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 349 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060800000
Metadata: PartitionId = 72057594054508544 Metadata: IndexId = 0
Metadata: ObjectId = 823673982 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 5 m_freeCnt = 1617
m_freeData = 8180 m_reservedCnt = 1606 m_lsn = (134:664:13)
m_xactReserved = 1606 m_xdesId = (0:18215) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

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

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E9DC060

0000000000000000: 30000800 01000000 0300f801 004f0661 ?0…O.a
0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000020: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
0000000000000030: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa
…省略…

Slot 1, Offset 0x6af, Length 1615, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x000000000E9DC6AF

0000000000000000: 30000800 02000000 0300f801 004f0662 ?0…O.b
0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000020: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000030: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000040: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000050: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
0000000000000060: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb
…省略…

a=3的记录所在槽2的内容如下:

Slot 2, Offset 0x1feb, Length 9, DumpStyle BYTE

Record Type = FORWARDING_STUB Record Attributes =
Memory Dump @0x000000000E9DDFEB

0000000000000000: 04055200 00010000 00???..R…

第一个字节的值为4(04)意味着这只是一个前转部分。后三个字节005205是记录移动的目的分页号。由于只是一个十六进制的值，需要将其转化成十进制值20997。下一组四个字节表明分页在文件号为1，槽号为0的地方。如果接着使用dbcc page来查看分页20997，可以看到前转记录的模样。

select CONVERT(bigint, CAST(0x005205 As varbinary));

20997

(1 行受影响)

use adventureworks;
GO
dbcc traceon(3604);
GO
dbcc page(adventureworks,1,20997,1)

输出结果如下:

PAGE: (1:20997)

BUFFER:

BUF @0x0000000080FF9180

bpage = 0x0000000080E46000 bhash = 0x0000000000000000 bpageno = (1:20997)
bdbid = 6 breferences = 0 bUse1 = 44564
bstat = 0xc0010b blog = 0x12121bbb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000080E46000

m_pageId = (1:20997) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 349 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594060800000
Metadata: PartitionId = 72057594054508544 Metadata: IndexId = 0
Metadata: ObjectId = 823673982 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 4865
m_freeData = 4952 m_reservedCnt = 0 m_lsn = (134:664:10)
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:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x6bb, Length 3229, DumpStyle BYTE

Record Type = FORWARDED_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0000000010F1C6BB

0000000000000000: 32000800 03000000 0300f803 00530693 ?2…S…
0000000000000010: 0c9d8c63 63636363 63636363 63636363 ?..ccccccccccccc
0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc
…省略…

OFFSET TABLE:

Row - Offset
0 (0x0) - 1723 (0x6bb)

前转指针
前转指针允许用户在堆中修改数据而无需担心对非聚集索引带来较严重的影响。如果一条已经被前转的记录必须再次移动，最初的前转指针就被更新并指向新的位置。如果还要继续移动，那么前转指针就会不断地指向另一个前转指针。另外，如果前转记录收缩到其原始位置足以能够容纳时，这条记录会被移回到原始位置，如果分页上还有可用空间的话，前转指针就会被删除。

SQL Server的后续版本可能会包括一些机制对堆表中的数据进行物理的重组，这将消除前转指针的作用。注意前转指针只存在于堆表中，并且alter table的重组选项对于堆表来说是不起作用的。可以对堆表上的非聚集索引进行碎片整理但是不能对表本身。目前来说，当前转指针创建完以后就永远呆在原地—除了个别例外情况。第一种例外已经提到过，行会收缩并返回其初始位置。第二种例外是数据库收缩时。当文件收缩时，SQL Server实际上会为书签进行再分配。收缩进程不会产生前转指针。对于因为收缩进程而被移除的分页来说，它们包含的任何前转指针或者残余部分实际上都处于“未前转”的状态。前转指针被移除的其他情况十分明显，譬如前转记录被删除或者在表上创建聚集索引从而使其不再是堆表结构。

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

sql server 数据修改的内部原理

select CONVERT(bigint, CAST(0x005205 As varbinary));

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品