MS SQL Server 架構

---

---

為了方便理解，我們將 SQL Server 的架構分為以下幾個部分：

• 總體架構
• 記憶體架構
• 資料檔案架構
• 日誌檔案架構

總體架構

客戶端 - 請求發起的地方。

查詢 - SQL 查詢，是一種高階語言。

邏輯單元 - 關鍵字、表示式和運算子等。

網路資料包 - 網路相關程式碼。

協議 - SQL Server 中有 4 種協議。

• 共享記憶體（用於本地連線和故障排除）。

• 命名管道（用於區域網連線）。

• TCP/IP（用於廣域網連線）。

• VIA - 虛擬介面介面卡（需要供應商設定專用硬體，並且從 SQL 2012 版本開始已棄用）。

伺服器 - SQL 服務安裝和資料庫駐留的地方。

關係引擎 - 真正執行的地方。它包含查詢解析器、查詢最佳化器和查詢執行器。

查詢解析器（命令解析器）和編譯器（翻譯器） - 檢查查詢語法並將查詢轉換為機器語言。

查詢最佳化器 - 以查詢、統計資訊和代數樹為輸入，準備執行計劃作為輸出。

執行計劃 - 就像路線圖一樣，包含查詢執行過程中所有步驟的順序。

查詢執行器 - 在執行計劃的幫助下逐步執行查詢，並聯系儲存引擎。

儲存引擎 - 負責在儲存系統（磁碟、SAN 等）上儲存和檢索資料、資料操作、鎖定和事務管理。

SQL OS - 位於主機（Windows 作業系統）和 SQL Server 之間。資料庫引擎上執行的所有活動都由 SQL OS 負責。SQL OS 提供各種作業系統服務，例如記憶體管理（處理緩衝池、日誌緩衝區）和使用阻塞和鎖定結構進行死鎖檢測。

檢查點程序 - 檢查點是一個內部程序，它將所有髒頁（已修改的頁）從緩衝區快取記憶體寫入物理磁碟。除此之外，它還將日誌記錄從日誌緩衝區寫入物理檔案。將髒頁從緩衝區快取記憶體寫入資料檔案也稱為髒頁硬化。

這是一個專用程序，由 SQL Server 以特定間隔自動執行。SQL Server 為每個資料庫單獨執行檢查點程序。檢查點有助於減少 SQL Server 在意外關機或系統崩潰/故障事件中的恢復時間。

SQL Server 中的檢查點

在 SQL Server 2012 中，有四種類型的檢查點：

• 自動 - 這是最常見的檢查點，它作為後臺程序執行，以確保 SQL Server 資料庫可以在恢復間隔 - 伺服器配置選項定義的時間限制內恢復。

• 間接 - 這是 SQL Server 2012 中的新增功能。它也在後臺執行，但為了滿足為配置了該選項的特定資料庫指定的特定使用者目標恢復時間。一旦為給定資料庫選擇了 Target_Recovery_Time，這將覆蓋為伺服器指定的恢復間隔，並避免對該資料庫進行自動檢查點。

• 手動 - 一旦發出檢查點命令，它就會像任何其他 T-SQL 語句一樣執行，直到完成。手動檢查點只針對當前資料庫執行。您還可以指定 Checkpoint_Duration（可選）- 此持續時間指定您希望檢查點完成的時間。

• 內部 - 作為使用者，您無法控制內部檢查點。在特定操作時發出，例如：

  • 關機會在所有資料庫上啟動檢查點操作，除非關機不乾淨（使用 nowait 關機）。

  • 如果恢復模式從完全/批次日誌更改為簡單。

  • 備份資料庫時。

  • 如果您的資料庫處於簡單恢復模式，則檢查點程序會在日誌滿 70% 或基於伺服器選項恢復間隔時自動執行。

  • 更改資料庫命令以新增或刪除資料/日誌檔案也會啟動檢查點。

  • 當資料庫的恢復模式為批次日誌記錄並且執行了最小日誌記錄操作時，也會發生檢查點。

  • 資料庫快照建立。

• 惰性寫入器程序 - 惰性寫入器將髒頁推送到磁碟的原因完全不同，因為它需要釋放緩衝池中的記憶體。當 SQL Server 處於記憶體壓力下時，就會發生這種情況。據我所知，這是由內部程序控制的，並且沒有對其進行設定。

SQL Server 持續監控記憶體使用情況以評估資源爭用（或可用性）；其工作是確保始終有足夠的可用空間。在此過程中，當它注意到任何此類資源爭用時，它會觸發惰性寫入器透過將髒頁寫入磁碟來釋放一些記憶體頁。它採用最近最少使用 (LRU) 演算法來決定哪些頁要重新整理到磁碟。

如果惰性寫入器始終處於活動狀態，則可能表示記憶體瓶頸。

記憶體架構

以下是記憶體架構的一些主要特徵。

• 所有資料庫軟體的主要設計目標之一是最小化磁碟 I/O，因為磁碟讀寫是最資源密集型操作之一。

• Windows 中的記憶體可以用虛擬地址空間來呼叫，該空間由核心模式（作業系統模式）和使用者模式（SQL Server 等應用程式）共享。

• SQL Server“使用者地址空間”分為兩個區域：MemToLeave 和緩衝池。

• MemToLeave (MTL) 和緩衝池 (BPool) 的大小由 SQL Server 在啟動時確定。

• 緩衝區管理是實現 I/O 高效率的關鍵元件。緩衝區管理元件由兩種機制組成：緩衝區管理器用於訪問和更新資料庫頁，緩衝池用於減少資料庫檔案 I/O。

• 緩衝池進一步細分為多個部分。最重要的部分是緩衝區快取記憶體（也稱為資料快取記憶體）和過程快取記憶體。緩衝區快取記憶體將資料頁儲存在記憶體中，以便可以從快取記憶體檢索經常訪問的資料。另一種方法是從磁碟讀取資料頁。從快取記憶體讀取資料頁透過最大限度地減少所需 I/O 操作的數量來最佳化效能，這些操作本質上比從記憶體檢索資料慢。

• 過程快取記憶體保留儲存過程和查詢執行計劃，以最大限度地減少必須生成查詢計劃的次數。您可以使用 DBCC PROCCACHE 語句查詢有關過程快取記憶體中大小和活動的資訊。

緩衝池的其他部分包括：

• 系統級資料結構 - 儲存有關資料庫和鎖的 SQL Server 例項級資料。

• 日誌快取記憶體 - 保留用於讀取和寫入事務日誌頁。

• 連線上下文 - 每個與例項的連線都有一個小記憶體區域來記錄連線的當前狀態。此資訊包括儲存過程和使用者定義函式引數、遊標位置等等。

• 堆疊空間 - Windows 為 SQL Server 啟動的每個執行緒分配堆疊空間。

資料檔案架構

資料檔案架構具有以下元件：

檔案組

可以將資料庫檔案組合到檔案組中，以進行分配和管理。沒有檔案可以是多個檔案組的成員。日誌檔案永遠不是檔案組的一部分。日誌空間與資料空間分開管理。

SQL Server 中有兩種型別的檔案組：主檔案組和使用者定義檔案組。主檔案組包含主資料檔案和未專門分配給另一個檔案組的任何其他檔案。系統表的頁面都分配在主檔案組中。使用者定義的檔案組是使用 create database 或 alter database 語句中的檔案組關鍵字指定的檔案組。

每個資料庫中的一個檔案組充當預設檔案組。當 SQL Server 將頁面分配給建立時未指定檔案組的表或索引時，頁面將從預設檔案組分配。要將預設檔案組從一個檔案組切換到另一個檔案組，它應該具有 db_owner 固定資料庫角色。

預設情況下，主檔案組是預設檔案組。使用者應該具有 db_owner 固定資料庫角色才能單獨備份檔案和檔案組。

檔案

資料庫有三種類型的檔案：主資料檔案、輔助資料檔案和日誌檔案。主資料檔案是資料庫的起點，並指向資料庫中的其他檔案。

每個資料庫都有一個主資料檔案。我們可以為主資料檔案指定任何副檔名，但推薦的副檔名是.mdf。輔助資料檔案是該資料庫中除主資料檔案以外的檔案。某些資料庫可能有多個輔助資料檔案。某些資料庫可能沒有任何輔助資料檔案。輔助資料檔案的推薦副檔名是.ndf。

日誌檔案儲存用於恢復資料庫的所有日誌資訊。資料庫必須至少有一個日誌檔案。一個數據庫可以有多個日誌檔案。日誌檔案的推薦副檔名是.ldf。

資料庫中所有檔案的所在位置都記錄在主資料庫和資料庫的主檔案中。大多數情況下，資料庫引擎使用來自主資料庫的檔案位置。

檔案有兩個名稱：邏輯名稱和物理名稱。邏輯名稱用於在所有 T-SQL 語句中引用檔案。物理名稱是 OS_file_name，它必須遵循作業系統的規則。資料和日誌檔案可以放在 FAT 或 NTFS 檔案系統上，但不能放在壓縮的檔案系統上。一個數據庫最多可以有 32,767 個檔案。

擴充套件

擴充套件是為表和索引分配空間的基本單元。一個擴充套件是 8 個連續頁或 64KB。SQL Server 有兩種型別的擴充套件：統一擴充套件和混合擴充套件。統一擴充套件僅由單個物件組成。混合擴充套件由多達八個物件共享。

頁

它是 MS SQL Server 中資料儲存的基本單元。頁的大小為 8KB。每頁的開頭是 96 位元組的頁首，用於儲存系統資訊，例如頁的型別、頁上的可用空間量以及擁有該頁的物件 ID。SQL Server 中有 9 種類型的資料頁。

• 資料 - 資料行，包含除文字、ntext 和影像資料之外的所有資料。

• 索引 - 索引條目。

• 文字/影像 - 文字、影像和 ntext 資料。

• GAM - 關於已分配擴充套件的資訊。

• SGAM - 關於系統級已分配擴充套件的資訊。

• 頁空閒空間 (PFS) - 關於頁面上可用空閒空間的資訊。

• 索引分配對映 (IAM) − 關於表或索引使用的擴充套件的資訊。

• 批次更改對映 (BCM) − 關於自上次備份日誌語句以來透過批次操作修改的擴充套件的資訊。

• 差異更改對映 (DCM) − 關於自上次備份資料庫語句以來已更改的擴充套件的資訊。

日誌檔案架構

SQL Server 事務日誌在邏輯上執行，就像事務日誌是一串日誌記錄一樣。每個日誌記錄都由日誌序列號 (LSN) 標識。每個日誌記錄都包含其所屬事務的 ID。

資料修改的日誌記錄要麼記錄執行的邏輯操作，要麼記錄修改資料的修改前和修改後映像。修改前映像是在執行操作之前的資料庫副本；修改後映像是操作執行後的資料庫副本。

恢復操作的步驟取決於日誌記錄的型別 −

• 記錄的邏輯操作。
  • 要向前回滾邏輯操作，請再次執行該操作。
  • 要向後回滾邏輯操作，請執行相反的邏輯操作。
• 記錄的修改前和修改後映像。
  • 要向前回滾操作，請應用修改後映像。
  • 要向後回滾操作，請應用修改前映像。

事務日誌中記錄了不同型別的操作。這些操作包括 −

• 每個事務的開始和結束。

• 每個資料修改（插入、更新或刪除）。這包括系統儲存過程或資料定義語言 (DDL) 語句對任何表的更改，包括系統表。

• 每個擴充套件和頁面分配或取消分配。

• 建立或刪除表或索引。

回滾操作也會記錄在日誌中。每個事務都會在事務日誌上預留空間，以確保存在足夠的日誌空間來支援由顯式回滾語句或遇到錯誤引起的回滾。當事務完成後，此預留空間將被釋放。

從必須存在才能成功進行資料庫範圍回滾的第一個日誌記錄到最後寫入的日誌記錄的日誌檔案部分稱為日誌活動部分或活動日誌。這是資料庫完全恢復所需的日誌部分。活動日誌的任何部分都不能被截斷。此第一條日誌記錄的 LSN 稱為最小恢復 LSN (Min LSN)。

SQL Server 資料庫引擎會在內部將每個物理日誌檔案劃分為多個虛擬日誌檔案。虛擬日誌檔案沒有固定大小，物理日誌檔案的虛擬日誌檔案數量也沒有固定數量。

資料庫引擎在建立或擴充套件日誌檔案時會動態選擇虛擬日誌檔案的大小。資料庫引擎嘗試維護少量虛擬檔案。管理員無法配置或設定虛擬日誌檔案的大小或數量。虛擬日誌檔案影響系統性能的唯一時間是當物理日誌檔案由較小的 size 和 growth_increment 值定義時。

size 值是日誌檔案的初始大小，growth_increment 值是每次需要新空間時新增到檔案中的空間量。如果由於許多小的增量導致日誌檔案增長到很大，則它們將具有許多虛擬日誌檔案。這可能會減慢資料庫啟動以及日誌備份和還原操作的速度。

我們建議您為日誌檔案分配接近最終所需大小的 size 值，並使用相對較大的 growth_increment 值。SQL Server 使用預寫日誌 (WAL)，它保證在將關聯的日誌記錄寫入磁碟之前，不會將任何資料修改寫入磁碟。這保持了事務的 ACID 屬性。