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

0000000000000000: 30000800 0a000000 0200fc01 004f0662 ?0…O.b

0000000000000010: 62626262 62626262 62626262 62626262 ?bbbbbbbbbbbbbbbb

0000000000000020: 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 @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

0000000000000000: 30000800 05000000 0200fc01 004f0661 ?0…O.a

0000000000000010: 61616161 61616161 61616161 61616161 ?aaaaaaaaaaaaaaaa

0000000000000020: 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 @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

0000000000000000: 30000800 0f000000 0200fc01 004f0663 ?0…O.c

0000000000000010: 63636363 63636363 63636363 63636363 ?cccccccccccccccc

0000000000000020: 63636363 63636363 63636363 63636363 ?cccccccccccccccc

0000000000000030: 63636363 63636363 63636363 63636363 ?cccccccccccccccc

…省略…

OFFSET TABLE:

Row - Offset

2 (0x2) - 3326 (0xcfe)

1 (0x1) - 1711 (0x6af)

0 (0x0) - 96 (0x60)

DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。

在拆分后檢查原來的數據分頁發現它既有可能包含原始分頁的上半部分的行記錄也可能是下半部分。SQL Server通常會移動記錄因而新插入的記錄會移動到新的分頁上去。因為這些行記錄無論如何都會移動，因此調整它們的位置來容納新插入的記錄就更具有意義了。在這個例子中，新的記錄，其聚集鍵值為22，會被插入到分頁的下半部分中去。因此當發生頁拆分時，前三行仍然留在原始分頁20996中?？梢詸z查頁頭來找到下一個包含新記錄的分頁位置。其中,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

0000000000000000: 30000800 19000000 0200fc01 004f0665 ?0…O.e

0000000000000010: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee

0000000000000020: 65656565 65656565 65656565 65656565 ?eeeeeeeeeeeeeeee

…省略…

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子句在分頁上預留一些空間來避免高峰時刻可能的系統崩潰?？梢栽谙到y運營最不繁忙的時雄姿英發定期使用期望的填充因子重建索引對系統進行優化。按照這種方式，在高峰時刻依然有額外的空間可以使用，并且可以因此節省拆分帶來的開鎖。如果系統不存在“不活躍”的時間，可以使用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

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 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會估計受到影響的記錄數，按照怎樣的方式訪問記錄（通過掃描，或者一次索引和索引獲?。┮约笆欠駮λ饕I進行修改。更新可以由查詢處理器或者存儲引擎來控制。在本小節中，我們只研究更新究竟按照原位替換的方式進行還是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實際上會為書簽進行再分配。收縮進程不會產生前轉指針。對于因為收縮進程而被移除的分頁來說，它們包含的任何前轉指針或者殘余部分實際上都處于“未前轉”的狀態。前轉指針被移除的其他情況十分明顯，譬如前轉記錄被刪除或者在表上創建聚集索引從而使其不再是堆表結構。

SQL SQL Server

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。