萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 內核架構深度剖析:從 TDS 封包到存儲引擎的完整執行流程

透過架構視角重新認識 SQL Server:從網路協議層 (TDS) 進入,經關聯引擎 解析/優化/編譯,至儲存引擎 存取方法/緩衝區管理/鎖管理/日誌管理,完整還原一條 SQL 語句的生命週期。

一、 歷史脈絡:從 Sybase 到 SQLOS 的演進

  • 1988 Sybase SQL Server 4.x (首個 PC 端 C/S 資料庫) → 1993 Microsoft 合作 Sybase → 1994 分道揚鑣。
  • 1995 SQL Server 6.0 (微軟首個獨立版本) → 1998 SQL Server 7.0 **重寫核心引擎** (行鎖、自動統計、索引重組) → 2000/2005 引入 **SQLOS** (自主排程、記憶體管理、同步物件) → 現代版本持續模組化。

二、 網路協議層:TDS (Tabular Data Stream)

  • Client 透過 **ODBC / OLE DB / ADO.NET / JDBC** 等資料存取介面,將命令編碼為 **TDS 封包**。
  • 傳輸層支援 **Shared Memory、Named Pipes、TCP/IP、VIA** (已棄用)。
  • 封包特性:Token-based 流控,請求/回應配對,支援多工、加密 (TLS)、壓縮。
  • Server 端入口:**Open Data Services (ODS) / Network Interface (NI)** —— 監聽連接、管理連接池、讀/寫緩衝區、TDS 解碼/編碼。

三、 關聯引擎:查詢處理管線

核心模組串聯:

  1. 命令剖析器:語法解析、語義綁定、物件解析 → 產出 **查詢樹 (Query Tree / Parse Tree)**。語法錯誤即終止。
  2. 查詢優化器:核心大腦。輸入查詢樹 + 統計資訊 + 中繼資料 → 搜尋最優執行計畫。
    • 階段:簡易優化 (Trivial Plan) → 完整優化 (Cost-Based,多階段:Search 0/1/2)。
    • 核心決策:索引選擇、連接順序/演算法 (Nested Loops/Hash/Merge)、平行度、運算子選擇。
    • 依賴 **統計資訊** 準確性 (自動/手動更新),錯誤統計 = 錯誤計畫。
  3. 查詢執行器:依執行計畫協調執行。不直接存取資料,而是發動儲存引擎模組:
    • 交易管理器 (Transaction Manager)
    • 鎖管理器 (Lock Manager)
    • 存取方法管理器 (Access Methods)
    • 緩衝區管理器 (Buffer Manager)

四、 儲存引擎:資料實體存取核心

1. 存取方法管理器

  • 根據執行計畫決定需存取的 **資料頁 (Data Page)**、索引頁 (Index Page)**、IAM 頁** 等。
  • 發出邏輯讀取請求給緩衝區管理器。

2. 緩衝區管理器 —— 記憶體核心

  • 管理 **Buffer Pool** (主要記憶體消費者),單位為 **8KB 頁**。
  • 核心邏輯:邏輯讀 (在記憶體中找到) vs 物理讀 (磁碟讀入)。
  • 預讀** - 依掃描模式預先非同步讀取後續頁面 (Read-Ahead)。
  • 寫入策略** - **Checkpoint** (定期刷髒頁)、**Lazy Writer** (記憶體壓力下淘汰髒頁)、**Eager Write** (大量插入時預寫)。
  • 頁面壽命期望 (PLE)** 關鍵指標:頁面在 Buffer Pool 平均存活秒數,< 300 秒常意味著記憶體壓力。

3. 鎖管理器與交易管理器

  • 鎖層級:Row → Page → Extent → Table → DB。模式:S, U, X, IS, IU, IX, Sch-S, Sch-M, BU。
  • 相容性矩陣決定阻塞/死結。
  • 隔離等級** 控制鎖持有時間/範圍:Read Uncommitted → Serializable → Snapshot (行版本控制,依賴 TempDB 版本存儲)。
  • 死結偵測:Lock Monitor 週期性掃描等待圖,選擇犧牲者 (低成本) Rollback。

4. 日誌管理器與 WAL (Write-Ahead Logging)

  • **核心鐵律**:「日誌先行」—— 任何資料頁修改前,對應的記錄檔記錄 (LSN) **必須先寫入磁碟 (強制寫入/Force Write)**。
  • 記錄檔結構:虛擬記錄檔 (VLF)、循環使用、LSN 有序鏈結。
  • 關鍵操作:Checkpoint 推進截斷點、備份記錄檔截斷非作用中 VLF、復原 (Redo/Undo) 依賴 LSN。

5. 其他關鍵管理器

  • 行管理器:頁面內資料列操作 (插入/刪除/更新、版本標記)。
  • 索引管理器:B-Tree 維護 (分裂/合併)、統計資訊採樣。
  • 空間管理器:配置/解配置 Extent (8 頁 = 64KB)、混合/統一 Extent、PFS/GAM/SGAM 頁追蹤。

五、 執行計畫快取與參數嗅探

  • 計畫存放於 **Plan Cache** (屬於 Buffer Pool 一部分),鍵值 = 查詢雜湊 + 參數/設定上下文。
  • 參數嗅探:首次編譯時「嗅探」參數值生成計畫,後續複用可能不適配 (參數敏感度問題)。
  • 緩解:`OPTION (RECOMPILE)`、`OPTIMIZE FOR UNKNOWN`、Plan Guides、Query Store 強制計畫。

六、 現代監控視圖 (DMV/DMF) 速查

結語

SQL Server 內核是一個精密的「協作式多工」系統:關聯引擎負責「想得清楚」(優化),儲存引擎負責「做得快」(存取)。DBA 的核心價值在於:**看懂執行計畫、讀懂等待統計、管好記憶體與索引、設計正確的架構**,讓這台精密機器在最佳狀態下運轉。

copyright © 萬盛學電腦網 all rights reserved
領域核心 DMV關鍵資訊
排程/Workersys.dm_os_schedulers, sys.dm_os_workers, sys.dm_os_tasksRunnable/Running/Wait 狀態、Worker 不足、Quantum 耗盡
記憶體/緩衝區sys.dm_os_buffer_descriptors, sys.dm_os_memory_clerks, sys.dm_os_performance_counters頁面分佈、Clerk 記憶體細目、PLE、Target/Total Server Memory
鎖/阻塞sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_exec_requests鎖資源/模式/狀態、阻塞鏈、等待類型/時間
執行計畫/查詢sys.dm_exec_query_stats, sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_sql_textCPU/IO/執行次數/計畫 XML/語句文本
索引/統計sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats, sys.dm_db_stats_propertiesSeek/Scan/Lookup/Update 計數、碎片率、統計資訊修改計數
I/O/檔案sys.dm_io_virtual_file_stats, sys.dm_io_pending_io_requests讀寫延遲/吞吐/停滯、掛起 I/O