記錄和數據記憶體演算法的描述，可在 SQL Server 中擴充數據可靠性

發行項
03/20/2024

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

摘要

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

若要深入瞭解引擎的基礎概念，以及復原和隔離惡意探索語意的演演算法 (ARIES) ，請參閱 1992 年 3 月 17 日第 1 卷下 (資料庫系統上的 ACM 交易檔) ：

外部連結： ARIES：使用 Write-Ahead 記錄來支援 Fine-Granularity 鎖定和部分復原的交易復原方法

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

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

本文中使用的詞彙

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

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

Write-Ahead 記錄 (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 (KB) 資料庫頁面中，針對每個 512 位元組扇區翻轉一點。如果當 SQL Server 稍後讀取頁面時，位處於錯誤的狀態，則頁面的寫入不正確;偵測到損毀頁面。復原期間偵測到損毀頁面，因為復原可能會讀取任何寫入不正確的頁面。

雖然 SQL Server 資料庫頁面為 8 KB，但磁碟會使用 512 位元組扇區來執行 I/O 作業。因此，每個資料庫頁面會寫入16個扇區。例如，如果系統因為電源故障 () 操作系統將前 512 個字節扇區寫入磁碟，以及完成 8 KB I/O 作業，而導致系統失敗，則可能會發生損毀頁面。如果在失敗之前成功寫入資料庫頁面的第一個扇區，磁碟上的資料庫頁面會顯示為更新，但可能尚未成功。

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

注意事項

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

記錄同位

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

效能影響

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

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 啟用來確保 WALFILE_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 磁碟驅動器上執行測試。此公用程式會模擬模擬數據裝置和記錄裝置的大量異步讀取/寫入活動。測試效能統計數據顯示已停用寫入快取的磁碟驅動器，以及介於 5,200 到 7,200 之間的 RPM 範圍，每秒的平均寫入作業數介於 50 到 70 之間。

如需公用程式的 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:D atabases 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 中擴充數據可靠性

摘要

本文中使用的詞彙

Write-Ahead 記錄 (WAL) 通訊協定

SQL Server和 WAL

SQL Server和穩定的記憶體

損毀頁面偵測

記錄同位

效能影響

扇區排序

寫入快取堆疊

自動寫入重新配置 (AWR)

數據安全

測試磁碟驅動器

數據裝置

增加效能

意見反應

意見反應

其他資源