共用方式為

SQL Server I/O 基本概念

  • 發行項

適用於：SQL ServerAzure SQL 受控執行個體Azure VM 上的 SQL Server

SQL Server 資料庫的主要用途是為了儲存和抓取資料，因此大量磁碟輸入/輸出 (I/O) 是資料庫引擎的核心特性。 因為磁碟 I/O 作業會消耗許多資源，而且需要相對較長的時間才能完成，所以 SQL Server 非常注重提高 I/O 的效率。

SQL Server 的儲存子系統提供了多種外形規格，包括機械磁碟機和固態儲存體。 本文提供如何使用磁碟快取準則來改善資料庫引擎 I/O 的詳細資料。

SQL Server 要求系統支援保證傳遞到穩定媒體，如 SQL Server I/O 可靠性程式需求中所述。 如需 SQL Server 資料庫引擎輸入和輸出需求的詳細資訊，請參閱 SQL Server 資料庫引擎磁碟輸入/輸出 (I/O) 需求。

磁碟 I/O

緩衝區管理員僅針對資料庫執行讀取和寫入。 其他檔案及資料庫作業，如開啟、關閉、擴充和壓縮，都是由資料庫管理員和檔案管理員元件來執行。

緩衝區管理員執行的磁碟 I/O 作業具有下列特性：

  • I/O 通常以非同步的方式進行，這可讓呼叫執行緒連續處理，而 I/O 作業則同時於背景中執行。 在某些情況下 (例如，不一致的記錄 I/O)，可能會發生同步 I/O。

  • 除非使用親和性 I/O 選項，否則所有 I/O 都是在呼叫執行緒中發出。 關連 I/O 遮罩選項會將 SQL Server 磁碟 I/O 繫結到指定的 CPU 子集。 在高階的 SQL Server 線上交易處理 (OLTP) 環境中，此延伸模組可強化 SQL Server 執行緒發出 I/O 的效能。

  • 多頁 I/O 是透過散佈 - 收集 I/O 來完成，可讓資料轉入或轉出非連續的記憶體區域。 這表示 SQL Server 可以快速填滿或排清緩衝快取，同時還能避免多個實體 I/O 要求。

長 I/O 要求

緩衝區管理員會報告任何至少持續 15 秒仍未處理的 I/O 要求。 這樣可以協助系統管理員區別 SQL Server 問題和 I/O 子系統問題。 報告錯誤訊息 MSSQLSERVER_833，並如 SQL Server 錯誤記錄中所顯示：

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

長 I/O 可以是讀取或寫入，但不會在目前的訊息中指出。 長 I/O 訊息是警告，而非錯誤。 讀取或寫入 I/O 都不會指出 SQL Server 的問題，但會指出基礎 I/O 系統的問題。 報告這些訊息的目的是在協助系統管理員更快找到 SQL Server 回應時間遲緩的原因，並區別 SQL Server 控制範圍之外的問題。 所以您不必採取任何動作，但系統管理員要調查 I/O 要求為什麼需要這麼長的時間，及該使用時間是否合理。

長 I/O 要求的原因

長 I/O 訊息會指出 I/O 已遭永久封鎖且永遠無法完成 (稱為遺失 I/O)，或只是仍未完成。 遺失 I/O 經常會導致閂鎖逾時，但也無法從訊息中辨別實際狀況。

長 I/O 通常表示 SQL Server 工作負載對磁碟子系統而言有些過重。 出現下列情形時，可能表示磁碟子系統已經不勝負荷：

  • 在繁重的 SQL Server 工作負載期間，錯誤記錄出現多則長 I/O 訊息。
  • 效能監視計數器顯示長時間的磁碟延遲、冗長磁碟佇列，或沒有磁碟閒置時間。

長 I/O 也可以是 I/O 路徑中的元件 (例如，驅動程式、控制器或韌體) 所造成，因為這個元件持續延後對舊有 I/O 要求的服務，而優先服務較新要求。 這可能會發生在互連環境中，例如 iSCSI 和 Fiber 通道網路 (可能是因為設定錯誤或路徑失敗)。 由於大部分的 I/O 都會立即提供，因此這種技術可能難以搭配效能監視器工具使用。 執行大量循序 I/O 的工作負載，如備份與還原、資料表掃描、排序、建立索引、大量載入以及清空檔案等作業，會使長 I/O 要求的情況惡化。

硬體或驅動程式問題會造成與上述情況無關的孤立長 I/O。 系統事件記錄檔可能包含有助於診斷問題的相關事件。

查詢或篩選驅動程式效率不佳導致的 I/O 效能問題

I/O 速度變慢可能是因為未有效寫入查詢，或未使用索引和統計資料正確調整所致。 I/O 延遲的另一個常見因素是存在防毒軟體或其他掃描資料庫檔案的安全性程式。 此掃描軟體可能會延伸至網路層，這就會增加網路延遲，進而間接影響資料庫延遲。 雖然描述的大約 15 秒 I/O 的案例在硬體元件中更為常見，但未最佳化的查詢或設定錯誤的防毒程式中會更經常觀察到較小的 I/O 延遲。

如需如何解決這些問題的詳細資訊，請參閱針對 I/O 所造成 SQL Server 效能緩慢的問題進行疑難排解。

如需如何在 SQL Server 上設定防毒軟體保護的資訊，請參閱設定防毒軟體以使用 SQL Server。

在儲存體控制器中寫入快取

由於硬碟啟動速率、移動磁碟機頭所需的機械時間以及其他限制因素，在機械磁碟機中，不使用快取執行的 I/O 轉移時間可能會大幅拉長。 SQL Server 安裝以提供快取控制器的系統為目標。 這些控制器會停用磁碟上的快取並提供穩定的媒體快取，以滿足 SQL Server I/O 需求。 它們會使用快取控制器的各種最佳化，來避免與儲存體搜尋和寫入時間相關的效能問題。

注意

某些儲存體廠商會使用持續性記憶體 (PMEM) 作為儲存體，而不是可改善整體效能的快取。 如需詳細資訊，請參閱為 Windows 上的 SQL Server 設定持續性記憶體 (PMEM)，以及為 Linux 上的 SQL Server 設定持續性記憶體 (PMEM)。

使用寫入快取 (也稱為回寫式快取) 儲存體控制器可以改善 SQL Server 效能。 如果寫入快取控制器和儲存子系統旨在用於資料關鍵性交易資料庫管理系統 (DBMS) 環境，則其對於 SQL Server 而言是安全的。 如果發生系統失敗，這些設計功能必須保留快取資料。 使用外部不斷電供應系統 (UPS) 通常不足以實現此目的，因為可能發生與電源無關的失敗模式。

快取控制器和儲存子系統可安全地供 SQL Server 使用。 大多數包含這些的全新專用伺服器平台都是安全的。 不過，您應該先向硬體廠商確認儲存子系統已經過測試和核准，可用於資料關鍵性關聯式資料庫管理系統 (RDBMS) 環境。

預寫記錄

SQL Server 資料修改陳述式會產生邏輯分頁寫入。 此寫入流可以描繪成兩個地點：記錄和資料庫本身。 基於效能考量，SQL Server 會透過其自己的快取緩衝區系統延遲寫入資料庫。 寫入記錄只會短暫延遲到 COMMIT 時間為止。 它們不會以與資料寫入相同的方式進行快取。 因為指定頁面的記錄寫入一律在頁面的資料寫入之前，所以記錄有時會稱為預寫記錄檔 (WAL)。

預寫記錄 (WAL) 通訊協定

通訊協定一詞是描述 WAL 的絕佳方式。 SQL Server 所使用的 WAL 稱為 ARIES (復原和隔離惡意探索語意的演算法)。 如需詳細資訊，請參閱管理加速資料庫復原。

這是一組已定義的特定實作步驟，可確保資料已正確儲存和交換，且在發生失敗時可復原至已知狀態。 如同網路包含定義的通訊協定，以一致且受保護的方式交換資料，WAL 也描述了保護資料的通訊協定。 所有 SQL Server 版本均使用 Win32 CreateFile 函式開啟記錄和資料檔案。 dwFlagsAndAttributes 成員會在 SQL Server 開啟時包含 FILE_FLAG_WRITE_THROUGH 選項。

FILE_FLAG_WRITE_THROUGH

SQL Server 使用 FILE_FLAG_WRITE_THROUGH 旗標建立其資料庫檔案。 此選項會指示系統透過任何中繼快取寫入，並直接移至儲存體。 系統仍然可以快取寫入作業，但無法延遲排清。 如需詳細資訊，請參閱 CreateFileA。

FILE_FLAG_WRITE_THROUGH 選項可確保當寫入作業傳回成功完成時，資料會正確儲存在穩定的儲存體中。 這與預寫記錄 (WAL) 通訊協定規格一致，可確保資料。 許多存放裝置 (NVMe、PCIe、SATA、ATA、SCSI 和 IDE 型) 包含 512 KB、1 MB 和更大的上線快取。 儲存體快取通常依賴電容，而不是電池供電式解決方案。 這些快取機制無法保證跨電源週期或類似的失敗點寫入。 它們僅保證磁區寫入作業的完成。 由於記憶體裝置的大小繼續增加，快取會變大，而且在失敗期間可能會公開較大的資料量。

如需 Linux 發行版本對 FUA 的支援，以及其對 SQL Server 影響的詳細資訊，請參閱 Linux 上的 SQL Server：強制單位存取 (FUA) 內部。

交易完整性和 SQL Server 復原

交易完整性是關聯式資料庫系統的基本概念之一。 交易會被視為完全套用或完全復原的不可部分完成的工作單位。 SQL Server 預寫交易記錄是實作交易完整性的重要元件。

任何關聯式資料庫系統也必須處理與交易完整性密切相關的概念，即從非計劃性系統失敗中復原。 多個非理想的真實效果可能會導致此失敗。 在許多資料庫管理系統上，系統失敗可能會導致冗長的人為導向手動復原處理序。

相反，SQL Server 復原機制則是自動的，而且不需要人為介入即可運作。 例如，SQL Server 可能支援任務關鍵性生產應用程式，並會因暫時的電源波動而發生系統失敗。 電源復原時，伺服器硬體會重新啟動、網路軟體會載入和初始化，而 SQL Server 則會重新啟動。 當 SQL Server 初始化時，它會根據交易記錄中的資料自動執行其復原處理序。 整個處理序會在沒有人為介入的情況下發生。 每當用戶端工作站重新啟動時，使用者就會發現其所有資料都存在，直至他們輸入的前次交易。

SQL Server 中的交易完整性和自動復原構成了強大的時間與人力節省功能。 如果寫入快取控制器未正確設計成用於資料關鍵性交易 DBMS 環境中，它可能會危害 SQL Server 的復原能力，因而損毀資料庫。 如果控制器會攔截 SQL Server 交易記錄寫入，並在控制器面板的硬體快取中進行緩衝，就會發生這種情況，但其不會在系統失敗期間保留這些寫入的頁面。

寫入快取和存放裝置控制器

大多數存放裝置快取控制器都會執行寫入快取。 無法一律停用寫入快取函式。

即使伺服器使用 UPS，也無法保證快取寫入的安全性。 可能會發生許多類型的系統失敗，而且 UPS 無法解決。 例如，記憶體同位錯誤、作業系統 (OS) 陷阱或造成系統重設的硬體故障可能會導致系統中斷。 硬體寫入快取中的記憶體失敗也可能導致重要記錄資訊遺失。

另一個與寫入快取控制器相關的潛在問題可能會發生在系統關機時。 在設定變更期間，循環 OS 或重新啟動系統並不罕見。 即使謹慎的操作員遵循 OS 建議，等待所有儲存體活動停止後再重新啟動系統，快取寫入仍會出現在控制器中。 按下 Ctrl+Alt+Del 按鍵組合，或按下硬體重新開機按鈕時，可以捨棄快取寫入，而這可能會損害資料庫。

您可以設計硬體寫入快取，以考慮捨棄已變更快取資料的所有可能原因，因此資料庫伺服器可安全使用這些資料。 其中一些設計功能包括攔截 RST 匯流排訊號，以避免不受控制的重設快取控制器、內建電池備份，以及鏡像或錯誤檢查和校正 (ECC) 記憶體。 請先向硬體廠商確認寫入快取包含這些和任何其他必要的功能，以避免資料遺失。

搭配使用儲存體快取與 SQL Server

資料庫系統首先負責準確儲存和擷取資料，即使在發生非預期的系統失敗時也是如此。

系統必須保證交易的不可部分完成性和持久性，同時亦要考慮目前的執行、多個交易和各種失敗點。 這通常稱為 ACID (不可部分完成性、一致性、隔離和持久性) 屬性。

本節會說明儲存體快取的影響。 建議您閱讀 Microsoft 知識庫中的下列文章，以進一步釐清快取和替代失敗模式討論：

另外，亦建議使用下列文件：

這兩個文件適用於目前支援的所有 SQL Server 版本。

電池供電式快取解決方案

增強型快取控制器系統會停用磁碟上的快取，並提供功能電池供電式快取解決方案。 這些快取可以將快取中的資料維護數天，甚至允許將快取擴充卡放在第二部電腦上。 正確還原電源時，在允許任何進一步的資料存取之前，會先完全排清未寫入的資料。 其中許多允許建立讀取與寫入快取的百分比，以實現最佳效能。 有些則包含大型記憶儲存體區域。 某些硬體廠商提供具有數 GB 快取的高端電池供電式磁碟機快取系統。 這些可以大幅改善資料庫效能。 使用電池供電式快取解決方案可提供 SQL Server 預期的資料持久性和一致性。

儲存子系統實作

有許多類型的子系統實作。 RAID (獨立磁碟容錯陣列) 和 SAN (存放區域網路) 是這類子系統實作的兩個範例。 這些系統通常會使用 SCSI 型磁碟機來建置。 這有幾個原因。 下一節一般會說明高階儲存體考量。

SCSI、SAS 和 NVMe

SCSI、SAS 和 NVMe 存放裝置：

  • 通常專為高負載用途而製造。
  • 通常以多使用者伺服器實作為目標。
  • 通常比其他實作具有更好的平均失敗率。
  • 包含複雜的啟發學習法，以輔助預測即將發生的失敗。

非 SCSI

其他磁碟機實作，例如 IDE、ATA 和 SATA：

  • 通常專為輕度和中等負載用途而製造。
  • 通常以單一使用者型應用程式為目標。

非 SCSI 桌面型控制器需要更多主要處理器 (CPU) 頻寬，而且經常受限於單一作用中命令。 例如，當非 SCSI 磁碟機調整損毀的磁區時，磁碟機會要求主機命令等待。 ATA 匯流排提供另一個範例：ATA 匯流排支援兩個裝置，但只有單一命令可處於作用中。 這就使得一個磁碟機處於閑置狀態，而另一個磁碟機為擱置命令服務。 RAID 系統採用桌面技術建置，皆會遇到這些徵兆，並會受到最慢響應者的極大影響。 除非這些系統使用進階設計，否則其效能不如 SCSI 型系統的效能那麼有效率。

儲存體考量

在某些情況下，桌面型磁碟機或陣列是適當的低成本解決方案。 例如，如果您為報告設定唯讀資料庫，則在停用磁碟機快取時，不應遇到 OLTP 資料庫的許多效能因素。

存放裝置大小會持續增加。 低成本、高容量磁碟機可能很吸引人。 但是，當您設定 SQL Server 的磁碟機和商務回應時間需求時，您應仔細考慮下列問題：

  • 存取路徑設計
  • 停用磁碟上快取的需求

機械硬碟

機械磁碟機使用旋轉磁盤來儲存資料。 提供有數種容量和外形規格，例如 IDE、SATA、SCSI 和序列連結 SCSI (SAS)。 某些 SATA 磁碟機包含失敗預測建構。 SCSI 磁碟機是針對較重的負載循環和降低的失敗率而設計。

磁碟機快取應停用，才能搭配使用磁碟機與 SQL Server。 預設會啟用磁碟機快取。 在 Windows Server 中，使用 [磁碟屬性]>[硬體] 索引標籤來存取 [屬性]>[原則] 索引標籤，進而控制磁碟機快取設定。

注意

某些磁碟機不接受此設定。 這些磁碟機需要特定的製造商公用程式，以停用快取。

IDE 和 ATA 型系統可以在執行活動 (例如損毀的磁區調整) 時延遲主機命令。 這可能會導致 I/O 活動停滯一段時間。

SAS 優點包括最高 256 個層級的進階佇列，以及佇列前端和不依序佇列。 SAS 後擋板旨在讓您在相同的系統中同時使用 SAS 和 SATA 磁碟機。

您的 SQL Server 安裝程式取決於控制器停用磁碟上快取的能力，並提供穩定的 I/O 快取。 只要控制器提供正確的穩定媒體快取功能，那麼將資料依序寫入到各種磁碟機就不會是 SQL Server 的阻礙。 控制器設計的複雜性會隨著進階資料安全性技術 (例如鏡像) 而增加。

固態儲存體

固態儲存體優於機械 (旋轉) 硬碟，但很容易遭受許多與轉動式媒體相同的失敗模式的影響。 固態儲存體會使用各種介面連線到您的伺服器，包括 NVM Express (NVMe)、PCI Express (PCIe) 和 SATA。 將固態媒體視為轉動式媒體，並確定針對電源故障採取適當的保護措施，例如電池供電式快取控制器。

電源錯誤造成的常見問題包括：

  • 位元損毀：記錄展現隨機位元錯誤。
  • 飛行寫入：語式正確的記錄最後會出現在錯誤位置。
  • 切邊寫入：部分作業的完成層級低於預期的磁區大小。
  • 中繼資料損毀：FTL 中的中繼資料已損毀。
  • 沒有回應的裝置：裝置完全無法運作，或大部分無法運作。
  • 無法可序列化：儲存體的最終狀態並非由可序列化的作業順序產生。

512e

大多數固態儲存體報告 512 位元組磁區大小，但在 1 MB 清除區塊內會使用 4 KB 頁面。 針對 SQL Server 記錄裝置使用 512 位元組對齊的磁區，可以產生更多讀取/修改/寫入 (RMW) 活動，而這可能會導致效能變慢和磁碟機磨損。

建議：請確定快取控制器知道存放裝置的正確頁面大小，而且可以適當地對齊實體寫入與固態儲存體基礎結構。

0xFFFFFFFF

新格式化磁碟機通常全為零。 已清除的固態裝置區塊全部為 1，讓清除區塊的原始讀取全部為 0xFF。 不過，在正常 I/O 作業期間，使用者讀取已清除的區塊並不常見。

模式壓印

我們過去使用的技術是將已知模式寫入整個磁碟機。 然後，當我們針對同一個磁碟機執行資料庫活動時，我們可以在意外出現模式時偵測到不正確的行為 (過時讀取/遺失寫入/讀取不正確的位移/等等)。

這項技術並不適用於固態儲存體。 寫入的清除和 RMW 活動會終結模式。 與轉動式媒體的磁區重複使用不同，固態儲存體記憶體回收 (GC) 活動、損耗調節、比例/設定為稍後瀏覽區塊和其他最佳化往往會導致寫入取得不同的實體位置。

韌體

相較於轉動式媒體對應項目，用於固態儲存體的韌體通常相當複雜。 許多磁碟機會使用多個處理核心來處理傳入要求和記憶體回收活動。 確定您已將固態裝置韌體保持在最新狀態，以避免已知問題。

讀取資料損毀和損耗調節

固態儲存體的常見記憶體回收 (GC) 方法有助於防止重複、讀取資料損毀。 重複讀取相同資料格時，電子活動可能會流失並導致鄰近資料格損壞。 固態儲存體會使用各種層級的錯誤修正碼 (ECC) 和其他機制來保護資料。

這類機制的其中一種與損耗調節相關。 固態儲存體會追蹤存放裝置上的讀取和寫入活動。 記憶體回收可以判定，相較於其他位置，作用點或位置的磨損速度更快。 例如，GC 會判定區塊在一段時間內處於唯讀狀態，而且需要移動。 此移動通常是針對具有更多磨損的區塊，因此原始區塊可用於寫入。 這有助於平衡磁碟機上的磨損，但將唯讀資料移至具有更多磨損的位置，並以數學方式增加失敗機會，即使略有增加。

SQL Server 可能會發生損耗調節的另一個副作用。 假設您執行 DBCC CHECKDB，並報告錯誤。 如果您第二次執行，則 DBCC CHECKDB 報告其他或不同錯誤模式的機會很小，因為固態儲存體 GC 活動可能會在 DBCC CHECKDB 執行之間進行變更。

OS 錯誤 665 和磁碟重組

轉動式媒體需要將區塊保持彼此接近，以減少磁碟機的頭移動並提高效能。 固態儲存體沒有實體前端，可消除搜尋時間。 許多固態裝置旨在允許並行處理不同區塊上的平行作業。 這表示不需要對固態媒體進行磁碟重組。 序列活動是最佳 I/O 模式，可將固態存放裝置上的 I/O 輸送量最大化。

注意

固態儲存體可從修剪功能獲益，該功能是作業系統 (OS) 層級命令，可清除已不再使用中的區塊。 在 Windows 中，使用最佳化磁碟機工具來設定每週排程，進而最佳化磁碟機。

建議：

  • 使用適當的電池供電式控制器，以最佳化寫入活動。 這可以改善效能、減少磁碟機磨損和實體分散層級。

  • 請考慮使用 ReFS 檔案系統來避免 NTFS 屬性限制。

  • 確定檔案成長大小已經過適當調整。

如需針對與分散相關的 OS 錯誤 665 進行疑難排解的詳細資訊，請參閱針對 SQL Server 檔案回報 OS 錯誤 665 和 1450。

壓縮

只要磁碟機維持穩定媒體的意圖，壓縮就可以延長磁碟機壽命，而且可能會對效能產生積極影響。 不過，某些韌體可能已經隱藏壓縮資料。 請記得先測試新的儲存體案例，然後再將其部署到實際執行環境。

摘要

  • 維護適當的備份及災害復原程序和處理序。
  • 讓您的韌體保持在最新狀態。
  • 仔細聆聽硬體製造指導。

快取考慮和 SQLIOSim

若要完全保護您的資料，您應確保已正確處理所有資料快取。 在許多情況下，這表示您必須停用磁碟機的寫入快取。

注意

請確定任何替代的快取機制都能正確處理多種失敗類型。

Microsoft 使用 SQLIOSim 公用程式對數個 SCSI 和 IDE 磁碟機執行測試。 此公用程式會將大量非同步讀取/寫入活動模擬至模擬的資料裝置和記錄裝置。 如需 SQLIOSim 的詳細資訊和詳細資料，請參閱使用 SQLIOSim 公用程式來模擬磁碟子系統上的 SQL Server 活動。

許多電腦製造商會訂購已停用寫入快取的磁碟機。 不過，測試顯示不一定總是這種情況，因此您應一律完整測試。 如果您有關於存放裝置快取狀態的任何問題，請聯絡製造商，並取得適當的公用程式或跳躍點設定，以停用寫入快取作業。

SQL Server 要求系統支援保證傳遞到穩定媒體，如 SQL Server I/O 可靠性程式需求中所述。 如需 SQL Server 資料庫引擎輸入和輸出需求的詳細資訊，請參閱 SQL Server 資料庫引擎磁碟輸入/輸出 (I/O) 需求。

相關內容