SQL Server 2012：SQL Server體繫結構——一個查詢的生命週期（第3部分）（完結）

標籤：

原文:SQL Server 2012：SQL Server體繫結構——一個查詢的生命週期（第3部分）（完結）

一個簡單的更新查詢

現在應該知道唯讀取資料的查詢生命週期，下一步來認定當你需要更新資料時會發生什麼。這個部分通過看一個簡單的UPDATE查詢，修改剛才例子裡讀取的資料，來回答。

慶幸的是，直到存取方法（Access Methods）前，更新操作和剛才SELECT語句流程是一模一樣的。

這次存取方法（Access Methods）需要修改資料，因此在I/O請求傳遞前，修改的細節要存放於硬碟。這個就是交易管理員（Transaction Manager）的工作。

交易管理員（Transaction Manager）

交易管理員（Transaction Manger）這裡有2個有趣的組件：鎖管理器（Lock Manager）和日誌管理器（Log Manager）。鎖管理器（Lock Manager）為資料提供並發性負責，它通過使用鎖傳遞配置的隔離等級。

備忘：

在剛才提到的SELECT查詢生命週期裡，鎖管理器（Lock Manager）也有用到，這裡繼續談的話會岔開話題，這裡它被提到因為它是交易管理員（Transaction Manager）的一部分。

這裡真正有趣的東西是日誌管理器（Log Manager），存取方法（Access Methods）代碼裡想要做出改變的請求被記錄，日誌管理器（Log Manager）把這些改變寫到交易記錄（transaction log）,這個就是預寫式日誌(Write-Ahead Logging：WAL)。

寫入交易記錄（transaction log）是資料修改事務的一部分，它總是需要物理寫入硬碟，因為即使在系統崩潰的時候，SQL Server可以靠它來重讀那些改變（在接下來的還原章節你會學到這個更多）。

在交易記錄（transaction log）裡實際存放的並不是修改語句清單，而是修改語句的結果造成頁面變更的細節。這是SQL Server為了可以撤銷修改，這也讓交易記錄（transaction log）內容很難讀懂，當然你可以藉助第三方工具來幫忙。

回到UPDATE查詢生命週期，更新操作已經被寫到日誌。當交易記錄已經確認物理寫入後，實際的資料才會修改。這也是為什麼交易記錄（transaction log）操作重要。

一旦存取方法（Access Methods）收到確認，它把修改請求發給緩衝區管理器（Buffer Manager）來完成。

交易管理員（Transaction Manager），存取方法（Access Methods），記錄我們更新的交易記錄（transaction log），完成資料修改請求的緩衝區管理器（Buffer Manager）。

緩衝區管理器（Buffer Manager）

需要修改的頁已經在緩衝裡了，緩衝區管理器（Buffer Manager）要做的只是修改需要的頁， 這個更新要求由存取方法（Access Methods）發起。在緩衝中的頁被修改後，確認會發回給存取方法（Access Methods），最後發回給用戶端。

這裡的關鍵點（也是最大的）是UPDATE語句只改變資料緩衝裡的資料，並不是在磁碟上的實際資料庫檔案。這樣做是基於效能的原因，現在這個也被稱為所謂的髒頁（Dirty Page），因為它和硬碟上對應頁是不一樣的。

這與ACID屬性裡定義的修改耐久性（durability of the modification）並不違背，因為你可以使用交易記錄重建改變。舉例來說，如果你伺服器突然斷電，實體記憶體（例如資料緩衝區）裡就啥都沒有了。髒頁是如何並在什麼時候寫回資料庫檔案在下一章節會介紹。

更新操作的生命週期如所示。緩衝區管理器（Buffer Manager）改變緩衝中頁的內容，並發送確認給存取方法（Access Methods）。可以看到，在此期間，資料檔案一直沒被訪問。

還原（Recovery）

在上一章節，你讀到了UPDATE查詢的生命週期，裡面談到了SQL Server使用預寫式日誌(Write-Ahead Logging：WAL)方法來保持任何更改的耐久性（durability of any changes）。

變更首先寫入交易記錄（transaction log），然後只停留在記憶體裡。這樣做是基於效能原因並使你需要撤銷的話可以從交易記錄（transaction log）裡還原。在還原章節會介紹更多的相關新概念和流程（new concepts  and terminology）。

髒頁（Dirty Pages）

從磁碟讀回記憶體的頁別標識為乾淨頁（clean page）因為它和它的副本是一樣的。同樣，一旦在記憶體裡的頁被修改會被標識為髒頁（Dirty Page）。

使用清空緩衝（DBCC DROPCLEANBUFFERS）可以從緩衝裡清掉乾淨頁（Clean pages）（註：從緩衝池中刪除所有緩衝區。），當你對開發與測試環境進行故障排除時非常方便，因為它強制從磁碟後續讀取來實現，不是緩衝，不接觸任何髒頁。

髒頁（dirty page）就是自硬碟載入到記憶體有改變且現在和磁碟上不一樣。用下面的動態視圖可以看每個資料庫有多少髒頁。

 

1 SELECT db_name(database_id) AS ‘Database‘,count(page_id) AS ‘Dirty Pages‘2 FROM sys.dm_os_buffer_descriptors3 WHERE is_modified =14 GROUP BY db_name(database_id)5 ORDER BY count(page_id) DESC

 

Database Dirty Pages

People   2524

Tempdb   61

Master   1
如上表示在People資料庫有20M的髒頁（2524 * 8 / 1024).
每當緩衝空閑不足或檢查點（checkpoint）發生時，這些髒頁（dirty page）會被定期寫回到資料庫。為了更快的分配頁，SQL Server總是在緩衝裡保持一定數量的可用空頁，這些可用空頁在可用緩衝列表裡被跟蹤。
當一個背景工作執行緒（worker thread）發起一個讀請求時，它在緩衝拿到64頁的列表並檢查這個緩衝列表是否低於特定的閾值（threshold）。如果是的話，會把列表裡的頁標記為到期（age-out），這會引起把任何髒頁（dirty page）寫回硬碟。另外一個稱為惰性寫入器（Lazy Writer）的線程也是基於空閑緩衝列表（free buffer list）不足。

 

惰性寫入器（Lazy Writer）

惰性寫入器（Lazy Writer）會定期檢查空閑緩衝列表（free buffer list）的大小。當值低的時候，它會掃描整個資料緩衝把有段時間沒用過的頁標記為到期（age-out），在記憶體裡標記它們為空白閑前會寫回硬碟。

惰性寫入器（Lazy Writer）也會在伺服器上監控可用實體記憶體，在記憶體非常不足的情況下會把空閑緩衝列表（free buffer list）的記憶體釋放回給系統。當SQL Server 很忙的時候，在還有可用實體記憶體和沒到伺服器最大記憶體配置閾值時，它會增大空閑緩衝列表（free buffer list）的大小來滿足（緩衝池（Buffer Pool）的）要求。

檢查點過程（Checkpoint Process）

檢查點（Check Point）是個SQL Server建立的時間點，用來保證任何提交的事務已經將它們的變更寫回硬碟。檢查點（Check point）成為資料庫可以開始的還原點。

檢查點過程（Check Point Process）用來保證已提交的事務相關的所有髒頁（dirty page）已經寫回硬碟。為了有效使用寫入器，它也會把未提交的髒頁（dirty page）也寫回硬碟，不像惰性寫入器（Lazy Writer），檢查點（Check Point）不會從緩衝中移除頁；它只把髒頁（dirty page）寫回硬碟並在快取頁面的頁頭將緩衝裡頁標記為乾淨。

預設情況下，在一個忙碌的伺服器裡，SQL Server會在每分鐘發起一次檢查點（Check Point），這會標記在交易記錄裡。如果SQL Server執行個體或資料庫重啟了，還原過程會讀取日誌來獲知，自上一個檢查點後的日誌裡不需要進行任何操作。

記錄序號（Log Sequence Number：LSN）

記錄序號（Log Sequence Number：LSN）在交易記錄裡標識記錄，它被排序的，因此SQL Server可以知道事件發生的順序。

像進行前滾或後滾的還原前，最小的LSN號會被拿到。考慮這個不僅是檢查點（Check Point）記錄序號（Log Sequence Number：LSN），還有其他更重要的。這就是說還原還需要擔心在檢查點（Check point）前，是否有髒頁（dirty page）還沒寫回硬碟。這個在有大量數目髒頁（dirty page）的大系統裡會發生。

因此檢查點（Check Point）之間的時間內，代表著大量的工作需要處理來，在上一個檢查點（Check Point）發生後，前滾任何提交的事務，或後滾任何沒有提交的事務。通過每分鐘的檢查點（Check Point），SQL Server嘗試保證還原時間自一個資料庫開始少於1分鐘，在此期間它不會自動執行檢查點（Check Point），除非有10MB的日誌寫入。

檢查點（Check Point）可以通過CHECK POINT的T-SQL命令人為執行，也可以由SQL Server裡的其它事件來觸發。例如，你發起一個備份命令時，檢查點（Check Point）會首先執行。

跟蹤號（trace flag）3502是檢查點（Check Point）開始和結束的錯誤號碼。例如，在自啟動後添加剛才的跟蹤號，在執行一系列的大量寫入後，我們在錯誤記錄檔裡可以看到如下的條目，可以看到檢查點（Check Point）在30-40秒之間執行一次。

 

跟蹤號（trace flag）提供改變SQL Server行為的一種途徑，通常協助我們進行故障排除或者出於測試目的啟用或停用特定的功能。有幾百個跟蹤號（trace flag）存在但官方只公開部分；點擊查看它的公開列表還有如何使用它：

 

恢複間歇（Recovery Interval）

恢複間歇（Recovery Interval）是個伺服器配置選項，可以用來調整檢查點（Check Point）間的時間差，因此可以設定自開始多少時間內的資料庫可以還原，恢複間歇（Recovery Interval）。

預設情況下，恢複間歇（Recovery Interval）設定為0；這會啟用SQL Server選擇一個合適的間歇（Interval），通常是接近於1分鐘自動執行一次檢查點（Check Point）。

改變這個值為大於0時，代表你希望在檢查點（Check Point）的之間的間歇時間大小。大多數情況下不沒必要修改，與還原時間比，你更在乎檢查點（Check Point）過程，你來決定是否設定。

恢複間歇（Recovery Interval）只在測試和實驗環境下才配置，為了有效停止自動檢查點（Check Point），出於監控東西的目的或擷取更好的效能，可以配置出奇很高值。除非你為了SQL Server趕上世界記錄速度，你不應該在真實生產環境裡修改這個值。

為了停止對磁碟子系統的太多影響，SQL Server甚至會抑制檢查點的I/O，因此它很會進行自我管理。如果你在伺服器上曾看到SLEEP_BPOOL_FLUSH的等待類型，這是因為SQL Server為了保持全域系統的效能進行了檢查點的I/O抑制。

還原方式（Recovery Models）

SQL Server有3種資料庫還原方式（Recovery Models）：完整（Full），批日誌（Bulk-Logged）和簡單（Simple）。你選擇的方式會影響到你交易記錄的使用方式，它增長到多大，你的備份策略，還有你的還原選項。

完整（Full）

使用完整（Full）還原方式，會要求所有操作已經在交易記錄裡完整寫入，在備份策略裡要求包含完整（Full）備份和交易記錄（transaction log）備份。

自SQL Server 2005開始，完整（Full）備份不清空（truncate）交易記錄（transaction log）。這樣做的話交易記錄備份的序列不會被損壞，在你完整備份損壞的情況提供一個額外的還原選項。

SQL Server資料庫如果需要更進階別的還原性，你應該使用完整（Full）還原方式。

批日誌（Bulk-Logged）

這是個很特殊的還原方式，想這樣做的話，是通過最小量的日誌寫入，來提高特定大量操作的效能。其他動作會和完整（FULL）還原方式一樣完全寫入日誌。因為只有復原事務需要的資訊被寫入日誌，這個方式可以提高效能。重做資訊沒有寫入日誌，這也表示你失去了基於時間點的還原（point-in-time-recovery）。

這些批日誌包括：

• 批量插入（BULK INSERT）
• 使用可執行檔BCP（Using the bcp executable）
• SELECT INTO
• 建立索引（CREATE INDEX）
• 修改索引重建（ALTER INDEX REBUILD）
• 刪除索引（DROP INDEX）

批日誌（Bulk-Logged）和交易記錄（Transaction Log）備份

使用批日誌（Bulk-Logged）模式是想讓你的批日誌（Bulk-Logged）操作更快完成。它不會為你的交易記錄（transaction log）備份減少磁碟空間需求。

簡單（Simple）

如果在資料庫上設定了簡單（Simple）還原，，每次檢查點（Check Point）裡發生的交易記錄裡，所有提交的事務都會被清空（truncate）。這是為了保證日誌大小保持在最小，並且不需要交易記錄（transaction log）備份（如果沒有的話），是好是壞看你對一個資料庫的還原層級要求。

 如果自上次的完整（FULL）或差異（differential）備份起潛在丟失的所有變更，仍滿足你的業務需要的話，你可以選擇簡單（Simple）還原。

SQL Server 2012：SQL Server體繫結構——一個查詢的生命週期（第3部分）（完結）