擴充 SQL Server 中資料可靠性的記錄和資料儲存演算法的描述

發行項
12/14/2024

原始產品版本： SQL Server 2014、SQL Server 2012、SQL Server 2008、SQL Server 2005
原始 KB 編號： 230785

摘要

本文討論如何Microsoft SQL Server 記錄和數據演算法擴充數據可靠性和完整性。

若要深入瞭解引擎的基礎概念，以及關於復原和隔離利用語意的演算法（ARIES），請參閱下列資料庫系統上的 ACM 交易檔（在 1992 年 3 月第 1 卷 1 下）：

外部連結： ARIES：支援使用預先寫入記錄的細微鎖定和部分回復的交易復原方法

文件說明 SQL Server 技術，以擴充與失敗相關的數據可靠性和完整性。

如需快取和替代失敗模式討論的詳細資訊，建議您閱讀Microsoft知識庫中的下列文章：

本文中使用的詞彙

在開始深入討論之前，下表會定義本文中所使用的一些詞彙。

詞彙	定義
電池支援	直接提供和本地化的電池備份設備，並由快取機制控制，以防止數據遺失。這不是不間斷的電源供應器（UPS）。 UPS 不保證任何寫入活動，而且可以從快取裝置中斷連線。
Cache	用來優化實體 I/O 作業並改善效能的中繼儲存機制。
Dirty Page	包含尚未排清至穩定記憶體之數據修改的頁面。如需有關髒頁緩衝區的詳細資訊，請參閱在《SQL Server 在線叢書》上撰寫頁面。內容也適用於 SQL Server 2012 和更新版本Microsoft。
失敗	任何可能導致 SQL Server 進程意外中斷的任何專案。範例包括：電源中斷、計算機重設、記憶體錯誤、其他硬體問題、不良扇區、磁碟驅動器中斷、系統失敗等等。
清除	強制快取緩衝區至穩定的記憶體。
閂鎖	用來保護資源實體一致性的同步處理物件。
非volatile 記憶體	任何在系統失敗中仍可取得的媒體。
已釘選頁面	保留在數據快取中的頁面，而且在穩定儲存位置中保護所有相關聯的記錄檔記錄之前，無法排清到穩定的記憶體。
穩定記憶體	與非volatile 記憶體相同。
揮發性記憶體	任何不會在失敗中保持不變的媒體。

預先寫入記錄（WAL）通訊協定

通訊協定一詞是描述 WAL 的絕佳方式。這是一組特定的和已定義的實作步驟，以確保數據已正確儲存和交換，而且如果發生失敗，可以復原至已知狀態。如同網路包含定義的通訊協定，以一致且受保護的方式交換資料，WAL 也描述了保護資料的通訊協定。

ARIES 檔會定義 WAL，如下所示：

WAL 通訊協定判斷提示代表某些數據變更的記錄檔記錄必須已經在穩定的記憶體中，才能允許變更的數據取代非揮發性記憶體中的舊版數據。也就是說，系統不允許將更新的頁面寫入至頁面的非大量儲存版本，直到記錄檔記錄至少復原部分為止，該記錄檔記錄會描述頁面的更新已寫入穩定記憶體。

如需預先寫入記錄的詳細資訊，請參閱《SQL Server 在線叢書》中的預先寫入事務歷史記錄主題。

SQL Server 和 WAL

SQL Server 使用 WAL 通訊協定。為了確保交易已正確認可，與交易相關聯的所有記錄檔記錄都必須在穩定的記憶體中受到保護。

若要釐清這種情況，請考慮下列特定範例。

注意

在此範例中，假設沒有索引，且受影響的頁面為第 150 頁。

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

接下來，將活動細分為簡單記錄步驟，如下表所述。

陳述式	執行的動作
BEGIN TRANSACTION	寫入記錄快取區域。不過，不需要排清到穩定的記憶體，因為 SQL Server 尚未進行任何實體變更。
INSERT INTO tblTest	1.數據頁 150 如果尚未提供，則會擷取到 SQL Server 數據快取。 2.頁面會閂鎖、釘選並標示為髒，並取得適當的鎖定。 3.已建置插入記錄檔記錄並新增至記錄快取。 4.新的數據列會新增至數據頁。 5.閂鎖已釋放。 6.與交易或頁面相關聯的記錄檔記錄目前不需要排清，因為所有變更都會保留在變動性記憶體中。
COMMIT TRANSACTION	1.已形成認可記錄檔記錄，且與交易相關聯的記錄檔記錄必須寫入穩定記憶體。在記錄檔記錄正確指派給穩定記憶體之前，不會將交易視為已認可。 2.數據頁 150 會保留在 SQL Server 數據快取中，而且不會立即排清到穩定的記憶體。當記錄檔記錄受到正確保護時，如有必要，復原可以重做作業。 3. 交易式鎖定已釋出。

請勿與「鎖定」和「記錄」等詞彙混淆。雖然重要、鎖定和記錄是處理 WAL 時的個別問題。在上一個範例中，SQL Server 通常會保留第 150 頁的闩鎖，以便執行頁面上的實體插入變更，而不是交易的整個時間。建立適當的鎖定類型，以在必要時保護數據列、範圍、頁面或數據表。如需鎖定類型的詳細資訊，請參閱《SQL Server 在線叢書》鎖定小節。

更詳細地查看範例，您可能會詢問 LazyWriter 或 CheckPoint 進程執行時會發生什麼情況。 SQL Server 會針對與臟和釘選頁面相關聯的事務歷史記錄記錄，發出所有適當的排清至穩定記憶體。這可確保在已排清相關聯的事務歷史記錄檔記錄之前，永遠無法寫入穩定記憶體的 WAL 通訊協定數據頁面。

SQL Server 和穩定記憶體

SQL Server 藉由包含磁碟扇區大小的知識（通常是 4,096 個字節或 512 個字節）來增強記錄和數據頁面作業。

若要維護交易的 ACID 屬性，SQL Server 必須考慮失敗點。在失敗期間，許多磁碟驅動器規格只保證扇區寫入作業數目有限。大部分規格都保證在發生失敗時完成單一扇區寫入。

SQL Server 會在扇區大小的倍數上使用 8 KB 的數據頁和記錄檔（如果已排清）。（大部分的磁碟驅動器使用 512 個字節作為預設扇區大小。如果發生失敗，SQL Server 可以藉由採用記錄同位和撕裂寫入技術來考慮大於扇區的寫入作業。

撕裂頁面偵測

此選項可讓 SQL Server 偵測因電源故障或其他系統中斷所造成的未完成 I/O 作業。若為 true，每當頁面寫入磁碟時，就會在 8 KB 的資料庫頁面中，針對每個 512 位元組扇區翻轉一個位。如果 SQL Server 稍後讀取頁面時，某個位處於錯誤狀態，則頁面寫入錯誤;偵測到撕裂的頁面。復原期間偵測到損毀頁面，因為復原可能會讀取任何未正確寫入的頁面。

雖然 SQL Server 資料庫頁面是 8 KB，但磁碟會使用 512 位元組扇區來執行 I/O 作業。因此，每個資料庫頁面會寫入16個扇區。如果系統在操作系統將前 512 位元組扇區寫入磁碟和完成 8 KB I/O 作業之間，系統失敗（例如電源故障）之間，可能會發生損毀頁面。如果在失敗前成功寫入資料庫頁面的第一個扇區，磁碟上的資料庫頁面將會顯示為更新，不過它可能未成功。

藉由使用電池支援的磁碟控制器快取，您可以確定數據已成功寫入磁碟或完全未寫入。在此情況下，請勿將撕裂頁面偵測設定為「true」，因為這是不需要的。

注意

SQL Server 中預設不會啟用損毀頁面偵測。如需詳細資訊，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。

記錄同位

記錄同位檢查類似於撕裂的頁面偵測。每個512位組的扇區都包含同位位。這些同位一律會以記錄檔記錄寫入，並在擷取記錄檔記錄時進行評估。藉由強制在512位元組界限上進行記錄寫入，SQL Server 可以確定認可作業會寫入實體磁碟區。

效能影響

所有版本的 SQL Server 都會使用 Win32 CreateFile 函式開啟記錄檔和數據檔。 dwFlagsAndAttributes 成員會在 SQL Server 開啟時包含 FILE_FLAG_WRITE_THROUGH 選項。

FILE_FLAG_WRITE_THROUGH 指示系統透過任何中繼快取寫入，並直接移至磁碟。系統仍然可以快取寫入作業，但無法延遲排清。

選項 FILE_FLAG_WRITE_THROUGH 可確保當寫入作業傳回成功完成時，數據會正確地儲存在穩定記憶體中。這會與 WAL 通訊協定一致，以確保數據。

許多磁碟驅動器（SCSI 和 IDE）都包含 512 KB、1 MB 或更大的上線快取。不過，磁碟驅動器快取通常依賴電壓，而不是電池支持的解決方案。這些快取機制無法保證跨電源週期或類似的失敗點寫入。它們僅保證磁區寫入作業的完成。這是 SQL Server 7.0 和更新版本內建撕裂寫入和記錄同位偵測的原因。隨著磁碟驅動器的大小繼續成長，快取會變大，而且在失敗期間可能會公開較大的數據量。

許多硬體廠商都提供電池支援的磁碟控制器解決方案。這些控制器快取可以維護快取中的數據數天，甚至允許將快取硬體放在第二部計算機中。正確還原電源時，會先排清未寫入的數據，再允許進一步的數據存取。其中許多允許建立讀取與寫入快取的百分比，以達到最佳效能。有些則包含大型記憶儲存體區域。事實上，對於市場的特定區段，某些硬體廠商提供具有 6 GB 快取的高端電池備份磁碟快取控制器系統。這些可以大幅改善資料庫效能。

進階快取實作會藉由不停用控制器快取來處理 FILE_FLAG_WRITE_THROUGH 要求，因為它們可以在系統重設、電源故障或其他失敗點時提供真正的重寫功能。

不需要使用快取的 I/O 傳輸可能會更長，因為移動磁碟驅動器頭、微調速率和其他限制因素所需的機械時間。

扇區排序

用來增加 I/O 效能的常見技術是部門排序。為了避免機械頭部移動，讀取/寫入要求會排序，讓頭部的動作更一致，以擷取或儲存數據。

快取可以同時保存多個記錄和數據寫入要求。 WAL 通訊協定的 WAL 通訊協定和 SQL Server 實作需要清除記錄寫入至穩定記憶體，才能發出頁面寫入。不過，使用快取可能會從記錄寫入要求傳回成功，而不會將數據寫入實際磁碟驅動器（也就是寫入穩定記憶體）。這可能會導致 SQL Server 發出數據頁寫入要求。

寫入快取參與時，數據仍被視為在變動性記憶體中。不過，從 Win32 API WriteFile 呼叫，確切地說 SQL Server 如何看到活動，取得成功的傳回碼。 SQL Server 或任何使用 WriteFile API 呼叫的進程只能判斷數據已正確取得穩定記憶體。

為了討論目的，假設數據頁面的所有扇區都會排序為在相符記錄檔記錄的扇區之前寫入。這會立即違反 WAL 通訊協定。快取會在記錄檔記錄之前寫入數據頁。除非快取完全支援電池，否則失敗可能會導致災難性的結果。

當您評估資料庫伺服器的最佳效能因素時，需要考慮許多因素。其中最重要的是「我的系統是否允許有效的 FILE_FLAG_WRITE_THROUGH 功能？

注意

您使用的任何快取都必須完全支援電池支援的解決方案。所有其他快取機制都容易發生數據損毀和數據遺失。 SQL Server 會啟用來盡一切努力確保 WAL FILE_FLAG_WRITE_THROUGH。

測試顯示，許多磁碟驅動器組態可能包含寫入快取，而不需要適當的電池備份。 SCSI、IDE 和 EIDE 磁碟驅動器充分利用寫入快取。如需 SSD 如何與 SQL Server 搭配運作的詳細資訊，請參閱下列 CSS SQL Server 工程師部落格文章：

SQL Server 和 SSD - RDORR 的學習筆記 - 第 1 部分

在許多組態中，正確停用 IDE 或 EIDE 磁碟驅動器寫入快取的唯一方法是使用特定製造商公用程式，或使用位於磁碟驅動器本身的跳線。若要確定磁碟驅動器本身已停用寫入快取，請連絡磁碟驅動器製造商。

SCSI 磁碟驅動器也有寫入快取。不過，操作系統通常會停用這些快取。如有任何問題，請連絡磁碟驅動器製造商以取得適當的公用程式。

寫入快取堆疊

寫入快取堆疊類似於扇區排序。下列定義是直接從領先的 IDE 磁碟驅動器製造商網站取得：

一般而言，此模式為作用中。寫入快取模式會接受主機將數據寫入緩衝區，直到緩衝區已滿或主機傳輸完成為止。

磁碟寫入工作會開始將主機數據儲存至磁碟。主機寫入命令會繼續接受並傳輸至緩衝區的數據，直到寫入命令堆棧已滿或數據緩衝區已滿為止。磁碟驅動器可能會重新排列命令，以優化磁碟驅動器輸送量。

自動重新設定（AWR）

另一種用來保護數據的常見技術，是在數據操作期間偵測不良扇區。下列說明來自領先的 IDE 磁碟驅動器製造商網站：

這項功能是寫入快取的一部分，可降低延遲寫入作業期間數據遺失的風險。如果磁碟寫入程式期間發生磁碟錯誤，磁碟工作會停止，且可疑扇區會重新配置到磁碟驅動器結尾的替代扇區集區。重新配置之後，磁碟寫入工作會繼續執行，直到完成為止。

如果針對快取提供電池備份，這可以是功能強大的功能。這會在重新啟動時提供適當的修改。最好偵測磁碟錯誤，但 WAL 通訊協定的數據安全性會再次要求進行即時，而不是以延遲的方式完成。在WAL參數中，AWR技術無法說明記錄寫入因為扇區錯誤而失敗，但磁碟驅動器已滿的情況。資料庫引擎必須立即知道失敗情況，才能正確中止交易、系統管理員可以收到警示，並採取正確的步驟來保護數據並更正媒體失敗狀況。

數據安全性

資料庫管理員應該採取幾項預防措施，以確保數據的安全性。

請務必確定備份策略足以從重大失敗中復原。異地儲存和其他預防措施是適當的。
經常在輔助資料庫或測試資料庫中測試資料庫還原作業。
請確定任何快取裝置都可以處理所有失敗情況（電源中斷、不良扇區、不良磁碟驅動器、系統中斷、鎖定、電源尖峰等等）。
請確定您的快取裝置：
- 具有整合式電池備份
- 可以在電源上重新發出寫入
- 如有必要，可以完全停用
- 實時處理不良扇區重新對應
啟用撕裂頁面偵測。（這對效能的影響不大。
如果可能的話，請設定 RAID 磁碟驅動器，以允許進行不良磁碟驅動器的熱交換。
使用較新的快取控制器，可讓您新增更多磁碟空間，而不需要重新啟動OS。這可以是理想的解決方案。

測試磁碟驅動器

若要完全保護您的數據，您應該確定已正確處理所有數據快取。在許多情況下，您必須停用磁碟驅動器的寫入快取。

注意

請確定替代快取機制可以正確處理多種失敗類型。

Microsoft已使用 SQLIOSim 公用程式對數個 SCSI 和 IDE 磁碟驅動器執行測試。此公用程式會將大量非同步讀取/寫入活動模擬至模擬的資料裝置和記錄裝置。測試效能統計數據顯示停用寫入快取的磁碟驅動器每秒 50 到 70 之間的平均寫入作業，以及介於 5,200 到 7,200 之間的 RPM 範圍。

如需公用程式的詳細資訊 SQLIOSim ，請參閱Microsoft知識庫中的下列文章：

如何使用 SQLIOSim 公用程式模擬磁碟子系統上的 SQL Server 活動

許多電腦製造商藉由停用寫入快取來訂購磁碟驅動器。不過，測試顯示這種情況不一定是這樣。因此，請一律完全測試。

數據裝置

在所有非記錄的情況下，SQL Server 只需要清除記錄檔記錄。執行非記錄作業時，數據頁也必須排清到穩定的記憶體;在發生失敗時，沒有任何個別記錄檔記錄可重新產生動作。

數據頁可以保留在快取中，直到 LazyWriter 或 CheckPoint 進程將它們排清到穩定的記憶體為止。使用WAL 通訊協定確保記錄檔記錄已正確儲存，以確保復原可以將數據頁面復原到已知狀態。

這並不表示建議將數據檔放在快取的磁碟驅動器上。當 SQL Server 將數據頁排清到穩定記憶體時，記錄檔記錄可以從事務歷史記錄中截斷。如果數據頁儲存在動態快取上，可能會截斷記錄檔記錄，以在發生失敗時用來復原頁面。請確定您的數據和記錄裝置都能正確容納穩定的記憶體。

提高效能

您可能會遇到的第一個問題是：「我有一個快取的 IDE 磁碟驅動器。但是，當我停用它時，我的表現變得低於預期。原因為何？

許多由 Microsoft 測試的 IDE 磁碟驅動器在 5,200 RPM 執行，以及 SCSI 磁碟驅動器為 7,200 RPM。當您停用 IDE 磁碟驅動器的寫入快取時，機械效能可能會成為一個因素。

為了解決效能差異，要遵循的方法很清楚：「處理交易速率」。

許多在線事務處理（OLTP）系統都需要高交易速率。針對這些系統，請考慮使用可適當支援寫入快取的快取控制器，並提供所需的效能提升，同時仍確保數據完整性。

若要觀察快取磁碟驅動器上 SQL Server 中發生的重大效能變更，使用小型交易會增加交易速率。

測試顯示，小於 512 KB 或大於 2 MB 的緩衝區高寫入活動可能會導致效能變慢。

請考慮下列範例：

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

以下是 SQL Server 的範例測試結果：

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

將整個一系列 INSERT 作業包裝成單一交易的程式，在所有設定中大約四秒內都會執行。這是因為所需的記錄檔排清數目。如果您未建立單一交易，則每個 INSERT 交易都會以個別交易的形式處理。因此，必須排清交易的所有記錄檔記錄。每個排清的大小都是512個字節。這需要大量的機械驅動介入。

使用單一交易時，交易的記錄檔記錄可以組合在一起，而且可以使用較大的寫入來排清收集的記錄檔記錄。這可大幅減少機械介入。

警告

我們建議您不要增加交易範圍。長時間執行的交易可能會導致過度和不必要的封鎖，並增加額外負荷。使用 SQL Server：Databases SQL Server 性能計數器來檢視事務歷史記錄型計數器。具體而言，Log Bytes Flushed/sec 可以指出許多可能導致高機械磁碟活動的小型交易。

檢查與記錄排清相關聯的語句，以判斷是否可以減少Log Bytes Flushed/sec 值。在上一個範例中，使用了單一交易。不過，在許多情況下，這可能會導致不想要的鎖定行為。檢查交易的設計。您可以使用類似下列程式代碼的程式代碼來執行批次，以減少頻繁和小型的記錄排清活動：

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server 要求系統支援保證傳遞至穩定媒體，如 SQL Server I/O 可靠性計劃檢閱需求下載檔中所述。如需 SQL Server 資料庫引擎輸入和輸出需求的詳細資訊，請參閱 Microsoft SQL Server 資料庫引擎輸入/輸出需求。

共用方式為

擴充 SQL Server 中資料可靠性的記錄和資料儲存演算法的描述

摘要

本文中使用的詞彙

預先寫入記錄（WAL）通訊協定

SQL Server 和 WAL

SQL Server 和穩定記憶體

撕裂頁面偵測

記錄同位

效能影響

扇區排序

寫入快取堆疊

自動重新設定（AWR）

數據安全性

測試磁碟驅動器

數據裝置

提高效能

意見反應

其他資源

共用方式為

擴充 SQL Server 中資料可靠性的記錄和資料儲存演算法的描述

摘要

本文中使用的詞彙

預先寫入記錄 （WAL） 通訊協定

SQL Server 和 WAL

SQL Server 和穩定記憶體

撕裂頁面偵測

記錄同位

效能影響

扇區排序

寫入快取堆疊

自動重新設定 （AWR）

數據安全性

測試磁碟驅動器

數據裝置

提高效能

意見反應

其他資源

預先寫入記錄（WAL）通訊協定

自動重新設定（AWR）