SQL SERVER 差異備份的解析

差異備份是以上次完整備份為基底，將到目前的差異備份出來。大多數人的問題或許跟我一樣：「系統如何比對上次完整備份到現在有哪些差異？」。

SQL SERVER是以8K 為一個Page，這是SQL SERVER的最小儲存單位；而每8個連續的page則組成一個 「Extent」。Extent是SQL SERVER空間管理的最小單位。

每一個資料檔案（mdf/ndf）都是由多個extent所組成。為了管理及紀錄這些extent的狀態。SQL SERVER的每個資料檔中，有一些系統的頁面，如GAM、SGAM、PFS、IAM…等。透過這些頁面，來紀錄資料檔中的頁面情況。

在這些系統的頁面中，有一個稱為DCM（Differential Changed Map）的頁面，每個資料檔至少有一個DCM頁面，它位於每個資料檔的第6頁（頁面編號從0起算）。

一個DCM頁可以保存63904個extent狀態訊息。每個DCM涵蓋了每個GAM的範圍。因此每隔511232頁，DCM會重複一個。第2個DCM頁會出現在第511238頁。

DCM是以bitmap的方式，來紀錄每個extent是否有過異動（0:未異動，1:有異動），當執行差異備份時，系統透過讀取DCM頁，來獲得哪些extent有異動，並備份那些有異動的extent，以完成差異的備份。但每次差異備份完，系統並不會去更改DCM的值，因此後續的差異備份動作，將會涵蓋之前的差異備份資料。

所以，差異備份的效能跟資料庫大小無關。差異備份的效能跟期間內的異動量相關。

差異備份實際測試

首先我們建立一個新的資料庫，並觀察它的DCM頁

create database TESTBAK

go

dbcc traceon(3604)

dbcc page(TESTBAK,1,6,3)

上述顯示的結果中，(1:0) 代表檔案編號1的第0頁。Extent並沒有所謂的ID來識別。通常以該extent的第1頁，來做為extent的識別碼。因此(1:0)，代表了第一個 extent，它包含了頁面編號1:0 ~ 1:7。至於CHANGED及NOT CHANGED，則表示了這些extent是否有被異動過 。

依上面的解釋，我們可以從上圖dbcc page的結果，計算出這個DB共有65個extent。

(（512-0）/8)+1 =65 （這個編號1的資料檔，共有65個extent）

當然，我們也可以直接用 dbcc showfilestats來查詢

接著，我們先對資料庫進行一次全備份，並再以dbcc page觀察DCM的情況

backup database TESTBAK to disk='c:\test\testbak.bak'

go

dbcc traceon(3604)
dbcc page(TESTBAK,1,6,3)

可以看到執行全備份後，DCM的bitmap有變化，大多數的DCM bit被變更為0（亦即NOT CHANGED），因為這些extent已經被備份了。

但或許你會覺得奇怪「全備份完後，不是所有的頁面應該都要標識成 NOT CHANGED才對，為什麼還是有些extent的狀態是CHANGED（已異動）」？

那是因為每次備份完成後，SQL Server會將有關該備份的訊息存儲在一些系統TABLE中。這會導致部分頁面在備份完後立即被更改，因此你永遠不會看到完全為（NOT CHANGED）的DCM。

DCM頁，Slot 1 offset 0xbe用於儲存bitmap，長度7992 bytes，扣除前4個bytes的record meta data。共有63904個bits可以用來存放extent的資訊。從頁頭0起算第194個byte開始，即為DCM bitmap。

為了再詳細的了解DCM，我們再次使用dbcc page將DCM頁的二進位資料顯示出來，跟上面的圖做一個比對。

dbcc page(TESTBAK,1,6,2)

上圖dbcc結果，DCM bitmap從0701…..開始。

DCM的解讀，要一個個byte來看：

例如

第一個byte：0x07=00000111 

從右邊開始看，每個bit代表一個extent的狀態。因此00000111，表示第1-3個extent，是CHANGED。第4-8個extent是NOT CHANGE。如下表示：

(1:0) CHANGED

(1:8) CHANGED

(1:16) CHANGED

(1:24) NOT CHANGED

(1:32) NOT CHANGED

(1:40) NOT CHANGED

(1:48) NOT CHANGED

(1:56) NOT CHANGED

第二個byte：0x01=00000001

一樣從右邊開始看，由於這是第二個byte，因此它代表的是從(1:64)開始

(1:64) CHANGED

(1:72) NOT CHANGED

(1:80) NOT CHANGED

(1:88) NOT CHANGED

(1:96) NOT CHANGED

(1:104) NOT CHANGED

(1:112) NOT CHANGED

(1:120) NOT CHANGED

第三個byte：0x08=00001000

一樣從右邊開始看，由於這是第三個byte，因此它代表的是從(1:128)開始

(1:128) NOT CHANGED

(1:136) NOT CHANGED

(1:144) NOT CHANGED

(1:152) CHANGED

(1:160) NOT CHANGED

(1:168) NOT CHANGED

(1:176) NOT CHANGED

(1:184) NOT CHANGED

以上是逐步解析DCM bitmap的紀錄方式，以便徹底了解DCM page。我們可以將上面解析的結果，跟之前的顯示結果圖對照一下，檢查是否相同。

接下來，我們試著建立一個table，並新增一筆資料

create table test0219(c1 int identity,c2 varchar(10))

go

insert into test0219 values('test dcm')

go 10

接著我們透過dbcc ind，來檢查這個table建立在哪些個page之上

dbcc ind('TESTBAK','test0219',1)

我們可以看到test0219這個table是建立在page 77，其IAM page是在page 78。

對應到extent，會是在(1:72).

原本的 (1:72) NOT CHANGED。

這時候我們再用dbcc page檢查DCM頁。

dbcc page(TESTBAK,1,6,3)

如上圖，(1:72)已經被標示成 CHANGED。至於為什麼還有其它的頁面也會被異動？那是因為我們新增的是一個table，這個資訊也會被紀錄在一些系統table之中（如sysobjects、syscolumns、sysindexes等），所以才會看到有其它的頁面也跟著被標示成CHANGED。

接著，我們做一次差異備份，根據之前所說，差異備份並不會去異動DCM頁。也因此後面的差異備份才會包含前面的差異備份。

backup database TESTBAK to disk='c:\test\testbak.dif' with differential

go

dbcc page(TESTBAK,1,6,3)

經過差異備份後，DCM頁仍然保持不動。

接著我們再做一次全備份

backup database TESTBAK to disk='c:\test\testbak.bak'

go

dbcc page(TESTBAK,1,6,3)

我們可以看到，經過全備份後，DCM頁會被歸零。但如前所述，每次備份後，SQL Server會將有關該備份的訊息存儲在一些系統TABLE中。這會導致部分頁面在備份完後立即被更改，因此你永遠不會看到完全為（NOT CHANGED）的DCM。

從以上的說明及實驗中，我們知道DCM page是用來紀錄從上次完整備份之後，有哪些extent被異動過。

當執行差異備份時，系統僅需檢查DCM page，就可以很快的將有異動過的extent備份出來。因此差異備份的速度快慢，與資料庫大小無關，差異備份的速度與異動量相關。

差異備份完成後，系統並不會去將DCM page歸零，因此後面的差異備份，將會涵蓋之前的差異備份。所以差異備份出來的檔案大小，會逐次的增加，直至下次完整備份為止。

SQL SERVER的三種備份方式中，全備份及差異備份，主要是針對「資料」，這兩種備份方式與交易紀錄較無關係（這兩種備份方式為了能夠recovery，它也會備走部份所需要的交易紀錄）。因此差異備份檔並不具有時間點還原的特性，當然如果有多個差異備份，你可以選擇距你想要還原的時間點最近的差異備份檔來還原。但你無法指定在某個差異備份檔案中還原到的特定時間點。

我做這個實驗的目地，主要是想更詳細的了解差異備份及其背後的原理。也在過程中，讓我學習到更深入的東西。

SQL SERVER 的臨界點（Tipping Point）

當一個索引搜尋無法涵蓋查詢所要求的欄位時，就會產生bookmark lookup（RID lookup / Key lookup），以取得查詢語句所需的欄位資料。

而bookmark lookup對效能而言，其實並不是一件好事。例如一個bookmark lookup的查詢，結果將返回10筆資料，就會形成10次的bookmark lookup，如果你用set statistics io on來觀察，這句查詢單是bookmark lookup就會產生10次的邏輯讀取，最後再加上原本的index seek的邏輯讀取，就是全部的邏輯讀取次數。

當bookmark lookup發生時，每返回一筆資料，就需要一次的邏輯讀取。

假設有個table，有3000筆資料，共佔用了100個page空間。如果是table scan的話，邏輯讀取次數也就是100。但如果是bookmark lookup，假設我們查詢的返回結果是200筆，就會造成大於200的邏輯讀取。這反而比table scan更慢更耗資源。

因此，SQLSERVER有個稱為「臨界點」（Tipping Point）的設置，當bookmark lookup產生時，查詢優化器會估計返回的筆數。當估計返回的筆數到達臨界點時，查詢優化器將直接採用scan的方式（table scan或clustered index scan）來執行此次查詢。

臨界點的計算是以table所佔用的page數來計算的：

臨界點 = 1/4 ~ 1/3 （total pages）

假設一個table共佔用100個page，那麼它的臨界點為：

100 * 1/4  ~ 100 * 1/3 = 25 ~ 33（臨界值範圍）

也就是當bookmark發生時， 如果估計查詢的筆數在大於25~33筆。就會改為scan的方式去讀取資料。

臨界點為什麼是一個範圍，而不是一個值呢。那是因為諸如索引碎片和陳舊的統計資訊，也可能影響優化器關於臨界點的決定。由於我們沒有優化器所具有的相同資訊，因此很難準確預測在特定的時間及特定的table的臨界點。這也是為什麼我們只能計算出一個可能的臨界值範圍。

底下我們做個實際的測試

drop table testlookup

go

create table testlookup(c1 int identity,c2 int ,c3 char(250))

go

insert into testlookup values(1,' This is a test string....')

go 3000

--建clustered index

create unique clustered index pk_1 on testlookup(c1)

go

--做假資料，將c2=c1，因為要建index

update testlookup set c2=c1 

go

create index idx1 on testlookup(c2)

go

Clustered Index共有2層，共101頁，如下圖

另外一個index(idx1)，也有2層，共7頁，如下圖

 

首先執行全表掃瞄

依前述的公式，這個table的臨界範圍應該會在：

101 * 1/4  ~ 101 * 1/3 = 25.25 ~ 33.33（臨界值範圍）

也就是說，當bookmark lookup查詢筆數到達這範圍(25.25 ~ 33.33)時，bookmark lookup，將會改為table scan。

執行條件式查詢，走IDX1的欄位，返回筆數估計為28筆。

從下圖中的執行計畫可以看到優化器選擇走bookmark lookup。

接下來我們將查詢條件放大，返回筆數估計29筆，可以看到優化器改走叢集索引掃瞄。而29這數字，確實落在臨界點範圍（25.25 ~ 33.33）中。

使用SQL Server時，經常會看到優化器做出與我所認知不相符的決定。在遇到這種情況時，我通常會去探究它背後的原因。

本篇所說的情況，也是我之前的困惑。我一直以為lookup再怎麼樣也應該會比scan快。現在回想起來，當時是基於對scan的誤解。以為只要看到scan，就表示有問題。但經過一番研究後，我發現事情並不是我本來所想像的那麼簡單。

應用不完全的知識常常導致不正確的結論。

~~共勉。

本篇是個人的心得，如果有錯誤的地方，也請大家不吝指導。

SQL SERVER頁面還原的疑問

SQL SERVER在執行頁面還原後（restore database ....page....with norecovery），通常還需執行 restore log。許多人會有這樣一個疑問，就是我們只是把某個頁面還原回來，但接著還原LOG時，並沒有（也不能）指定頁面，而是必需還原整個LOG。而LOG中包含了許多「非我們想還原的頁面」，這樣會不會造成LOG中的操作被重覆執行？

每個人可能都會說：「當然是不會...」，

接下來，我想就個人的理解，解釋一下「為什麼...」

SQL SERVER頁面header的資訊中，有個m_lsn，這個代表了這個頁面最後被異動的LSN號碼。每當這個頁面的資料有異動時，頁面的m_lsn即會被更新。

Page Header的m_lsn，對應到Transaction Log的LSN。當restore log時，如果一個log在restore，當它要去異動某個頁面時，它會用Transaction Log的LSN跟頁面上的m_lsn去比對，當m_lsn小於 LSN 時，這個異動才會restore到這個頁面上。

這樣的機制，主要是用在「頁面還原」時，當我們執行

RESTORE DATABASE PAGE='1:57, 1:202, 1:916, 1:1016' 

   FROM   

   WITH NORECOVERY; 

RESTORE LOG FROM   

   WITH NORECOVERY; 

RESTORE LOG FROM   

   WITH NORECOVERY;  

某頁面損壞時，先從備份檔把某頁面還原。接著再還原後續的交易紀錄。

由於是頁面還原，正常的頁面還是保留在最新狀態。我們原本的疑問是，這時再還原LOG，那麼會不會造成一些重覆性的操作。

答案是不會，因為restore log時，必需是頁面的m_lsn小於LSN 才會被異動。因此也保證了頁面還原的可行性。

以下做個簡單的測試：

create database PHIL

go

use PHIL

go

create table test0120(c1 int identity,c2 char(500))

go

insert into test0120 values('abcd')

go 5

上面，建立一個資料庫，並塞入幾筆資料

dbcc ind(PHIL,test0120,1)

go

以下做個簡單的測試：

create database PHIL

go

use PHIL

go

create table test0120(c1 int identity,c2 char(500))

go

insert into test0120 values('abcd')

go 5

上面，建立一個資料庫，並塞入幾筆資料

dbcc ind(PHIL,test0120,1)

go

dbcc traceon(3604)

dbcc page(PHIL,1,119,2)

go

我們利用工具將頁面119的m_lsn改成34:94:2

接著執行資料庫備份，此時在備份檔中的頁面是我改過了m_lsn

backup database PHIL to disk='d:\backup\phil.bak' with init

刪除某筆資料

delete test0120 where c1=3

再備份交易紀錄

backup log PHIL to disk='d:\backup\phil.trn' with init

上面是一個典型的資料庫備份過程，照理說，我們如果將資料庫復原，再將log復原，test0120這個table的c1=3的資料，應該會不在。

但由於在資料庫備份檔中的m_lsn被我們改成較大的值，依前面的說明，restore log時，這個頁面的lsn較新，因為這筆交易將不會復原。

接著我們測試看看

首先還原資料庫，並置於norecovery狀態

restore database PHIL from disk='d:\backup\phil.bak' with replace,norecovery

再來還原交易紀錄

restore log PHIL from disk='d:\backup\phil.trn'

檢查c1=3的紀錄，正常的情況下，這筆紀錄應該是不在

select * from phil..test0120

但我們可以看到上圖，c1=3的這筆紀錄依然存在，表示那筆交易紀錄並沒有apply。

無論是還原交易紀錄或者是還原頁面，其實都是以頁面的LSN為「比較點」，當頁面的m_lsn < Log LSN，這筆交易（或者頁面）才會被還原。