SQL Server 內部架構深度解析：從 TDS 封包到儲存引擎的完整追蹤

理解 SQL Server 內部架構，是從「會用」進階到「會調優、會除錯、會架構」的關鍵門檻。本文從網路協議層 (TDS) 切入，逐層剖析關聯引擎 (查詢剖析、優化、執行) 與儲存引擎 (存取方法、緩衝區、鎖、日誌、檢查點) 的協作流程，還原一條 SQL 從客戶端發送到結果返回的完整生命週期。

歷史脈絡：從 Sybase 到雲原生

• 1988: Sybase SQL Server 1.0 (OS/2) — 微軟與 Sybase 合作
• 1993: SQL Server 4.2 — 微軟獨立發行
• 1995: SQL Server 6.0 — 微軟完全自主研發
• 1998: SQL Server 7.0 — 重寫儲存引擎、支援行鎖、自動管理
• 2005: SQL Server 2005 — SQLOS、CLR、DMV、Service Broker、資料庫鏡像
• 2012: AlwaysOn 可用性群組、Columnstore、含資料庫
• 2017: Linux 支援、圖形處理、自動調優
• 2022: 智慧查詢處理、分類帳帳、Azure Arc 整合

網路層：TDS (Tabular Data Stream)

• 應用層協議，封裝在 TCP/IP、Named Pipes、Shared Memory、VIA 之上
• Token-based 流控：客戶端送請求 Token，伺服器回應 Row/Metadata/Done/Error Token
• 客戶端驅動程式 (ODBC/OLE DB/ADO.NET/JDBC) 負責編碼/解碼 TDS
• Open Data Services (ODS)：引擎進入點，監聽端點、管理連線池、讀/寫緩衝區、TDS 解析

關聯引擎：查詢處理管線

1. 命令剖析器： 語法檢查、解析樹生成、名稱解析 (sys.objects、sys.columns)、綁定 (類型推導、隱式轉換)。輸出：邏輯運算子樹。
2. 查詢優化器： 成本基礎優化 (CBO)。輸入：邏輯樹 + 統計資訊 + 中繼資料。階段：簡化 → 探索 (尋找等價規則) → 搜尋 (Memo 結構、動態規劃) → 最佳物理計畫。核心決策：連接順序、連接演算法 (Nested Loops/Hash/Merge)、索引選擇、平行度、記憶體授予。
3. 查詢執行器： 將物理計畫實例化為執行緒任務，協調儲存引擎、鎖管理器、記憶體授予、平行交換 (Gather/Repartition/Exchange)。

儲存引擎：資料存取與交易管理核心

存取方法管理器

• 介面：IRowset、IRowsetIndex、IRowsetScroll
• 負責：索引搜尋/掃描、書籤查閱、資料列定位、分頁/合併頁面
• 不直接讀寫磁碟，透過緩衝區管理器

緩衝區管理器 — 核心效能中樞

• 管理 8KB 頁面在記憶體 (Buffer Pool) 的生命週期
• 頁面狀態：Clean / Dirty / I/O Pending / Latched
• 替換策略：Clock 掃描算法 (近似 LRU)，參考計數、檢查點 LSN
• 預讀：大範圍掃描時非同步預載入後續Extent (64KB/8頁)
• 惰性寫入器：背景執行緒定期刷新 Dirty 頁面至磁碟，保持 Buffer Pool 可用空間

鎖管理器與交易管理器

交易隔離 透過鎖持有時間、版本存儲 (SNAPSHOT) 實現。死鎖偵測器 週期性掃描等待圖，選擇犧牲者 (依 DEADLOCK_PRIORITY、已做工作量) 回滾。

日誌管理器與檢查點 — 恢復基石

• WAL (Write-Ahead Logging)：任何資料頁修改前，對應日誌記錄必須先寫入磁碟 (強制寫入、不經緩衝區)
• 日誌記錄類型：LOP_INSERT_ROWS, LOP_MODIFY_ROW, LOP_DELETE_ROWS, LOP_BEGIN_XACT, LOP_COMMIT_XACT, LOP_CHECKPOINT
• 檢查點：定期將 Dirty 頁面刷新、截斷非作用中 VLF、記錄檢查點 LSN。加速啟動恢復 (僅重做/回滾檢查點後的日誌)。
• 恢復階段：Analysis (掃描日誌建立 Dirty Page Table、Active Xact Table) → Redo (重做所有已提交) → Undo (回滾未提交)。

關鍵 DMV 觀測內部狀態

-- 緩衝區命中率
SELECT 
    (1 - (physical_reads * 1.0 / (physical_reads + logical_reads))) * 100 AS HitRatio
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';

-- 等待統計 (找出瓶頸類別)
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('LAZYWRITER_SLEEP','SLEEP_TASK','DIRTY_PAGE_POLL',...)
ORDER BY wait_time_ms DESC;

-- 執行計畫快取分析
SELECT 
    cp.objtype, cp.usecounts, cp.size_in_bytes,
    st.text, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE cp.objtype = 'Adhoc' AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

架構演進趨勢

• SQLOS → SQLPAL (Platform Abstraction Layer)： Linux 移植層，統一 Windows/Linux 核心行為
• 智慧查詢處理 (IQP)： 批次模式自適應連接、記憶體授予回饋、表變數延遲編譯、純量 UDF 內聯
• 加速資料庫恢復 (ADR)： 使用持久化版本存儲 (PVS) + 邏輯日誌掃描 (sLog)，將恢復時間從「日誌大小」降為「作用中交易數」
• 分類帳帳： 區塊鏈式防竄改資料表

深入內部架構，不只是學術練習，而是賦予你在面對「CPU 100% 但查詢不多」、「備份還原卡在恢復階段」、「平行查詢越跑越慢」等疑難雜症時，能從根因切入、精準施策的能力。

1. 上一篇文章： SQL Server 索引管理六大鐵律：從建立策略到維護實務的完整法則
2. 下一篇文章： SQL Server 動態樞紐分析：透過動態 SQL 實現資料透視表自動產生

關於SqlServer

設定 SQL Server 2005 Analysis Services HTTP 存取完整步驟

概述 SQL Server 2005 Analysis Se

SQL Server 錯誤記錄清理與無記錄檔資料庫修復：sp_cycle_errorlog、DBCC REBUILD_LOG 實戰

一、錯誤記錄暴增問題：Query Notification

SQL Server 2000 錯誤日誌「Successfully re-opened the local eventlog」解決方案與風險警示

SQL Server 2000 錯誤日誌中出現「Unable

SQL Server 2012 附加資料庫錯誤 5120 解決方案兩則

錯誤現象 SSMS 附加 .mdf 時彈出：SQL Serv