SQL Server 2005 架構

1. SQL Server 引擎概觀

SQL Server 包含四大核心元件：協定層、關聯式引擎（又稱查詢處理器）、儲存引擎 與 SQLOS。任何用戶端應用程式提交給 SQL Server 執行的批次都必須與這四個元件互動。

1.1 協定層

負責接收請求並轉換為關聯式引擎可識別的形式；同時取得查詢結果、狀態資訊、錯誤資訊，轉換為用戶端可理解的格式後傳回。

1.2 關聯式引擎

負責接收 SQL 批次並決定處理方式。對 T-SQL 查詢與程式結構，可解析、編譯、最佳化請求並檢查執行過程。若執行時需要資料，會送出資料請求給儲存引擎。

1.3 儲存引擎

管理所有資料存取，包含交易式命令與大量作業（備份、大量插入、DBCC 命令等）。

1.4 SQLOS

處理通常視為作業系統職責的活動：執行緒管理（排程）、同步基元、死結偵測、記憶體管理（含緩衝集區）。

1.5 如何觀測資料庫引擎行為

SQL Server 2005 引入動態管理檢視（DMV）與動態管理函數（DMF），讓開發人員與 DBA 能觀測以前無法看到的內部資訊。這些中繼資料存在於 sys 結構描述中，名稱皆以 dm_ 開頭。常用類別：

• dm_exec_*：使用者程式碼執行與資料庫連線相關資訊
• dm_os_*：儲存、鎖定、排程等系統底層資訊
• dm_tran_*：目前交易的詳細資訊
• dm_io_*：網路與磁碟 I/O 活動追蹤
• dm_db_*：資料庫與資料庫物件（如索引）詳細資訊

2. 協定層詳細

應用程式與 SQL Server 溝通時，協定層使用微軟定義的表格式資料流（TDS）封包規範通訊格式。伺服器端與用戶端皆有網路程式庫將 TDS 封包封裝為標準通訊協定（TCP/IP、命名管道等）封包。可用協定：

• 共享記憶體：最簡單、無需設定
• 命名管道：區域網路開發
• TCP/IP：網際網路最廣泛使用
• VIA：搭配 VIA 硬體的專用協定

3. 關聯式引擎子元件

3.1 命令剖析器

處理傳送給 SQL Server 的 T-SQL 語言事件，檢查語法正確性並轉譯為可執行的內部格式（查詢樹）。

3.2 查詢最佳化器

從命令剖析器取得查詢樹並準備實際執行。不可最佳化的陳述式（控制流程、DDL 命令）編譯為內部格式；可最佳化的陳述式（DML：SELECT、INSERT、UPDATE、DELETE）標記後傳送給最佳化器。最佳化器編譯整個批次、最佳化查詢並檢查安全性，產出執行計畫。

3.3 SQL 管理器

管理預存程序與執行計畫相關事務，判斷何時需重新編譯、管理預存程序緩衝區以供重用、負責查詢自動參數化。

3.4 資料庫管理器

管理查詢編譯與最佳化所需的中繼資料存取。中繼資料儲存於儲存引擎管理的資料中，但某些元素（資料行資料類型、資料表可用索引）必須在查詢執行前即可存取。

3.5 查詢執行器

執行查詢最佳化器產生的執行計畫，如調度員逐步執行計畫中每個命令。大多數命令需與儲存引擎互動以修改或取回資料、管理交易與鎖定。

4. 儲存引擎與 SQLOS

4.1 存取方法

當 SQL Server 需定位資料時呼叫存取方法程式碼，建立與請求掃描資料頁與索引頁，準備 OLE DB 資料列集回傳給關聯式引擎。存取方法不直接取回資料頁，而是向緩衝區管理器發出請求。

4.2 交易服務

核心特性：保證交易原子性（全做或全不做）、持久性（提交後即使系統故障也能復原）。交易具備 ACID 四屬性：原子性、一致性、隔離性、持久性。

4.3 SQLOS 詳細

SQL Server 2005 將所有需存取作業系統的服務歸納為單一功能單元 SQLOS，如同 SQL Server 內部的作業系統。提供記憶體管理、工作排程、I/O 管理、鎖與交易管理框架、死結偵測、副本製作、例外處理等通用功能。

5. 排程器

5.1 SQL Server 背景工作

排程器可視為背景工作使用的邏輯 CPU。背景工作可為執行緒或綁定到邏輯排程器的纖程。若設定親和性遮罩，每個排程器對應特定 CPU。每個排程器依需求建立與銷毀背景工作。背景工作空閒超過 15 分鐘或記憶體緊張時可能被銷毀。

5.2 排程器狀態與動態親和性

SQL Server 2005 每個 CPU 啟動時建立一個排程器執行緒。排程器狀態為線上或離線，取決於親和性遮罩設定。可動態控制處理器親和性：改變遮罩納入額外 CPU 時，新 CPU 上線，排程器監視器偵測負載不平衡並遷移背景工作；使 CPU 離線時，該排程器完成現有工作後停止。

5.3 觀測排程器內部狀態

主要 DMV：sys.dm_os_schedulers、sys.dm_os_workers、sys.dm_os_tasks、sys.dm_os_waiting_tasks。需具備「檢視伺服器狀態」權限。

6. 記憶體管理

6.1 緩衝集區與資料快取

SQL Server 主要記憶體元件為緩衝集區。未被其他元件使用的記憶體保留於緩衝集區，作為從磁碟讀取資料庫頁面的資料快取。緩衝管理器管理磁碟 I/O，將資料頁與索引頁置於快取供多用戶共享。緩衝區即記憶體中的一個資料頁（8 KB），可視為容納資料庫頁面的頁框架。

6.2 存取記憶體中的資料頁

快取中的資料經雜湊處理以支援高速存取。雜湊表為記憶體結構，包含指向緩衝區頁面的指標鏈結清單。

6.3 管理資料快取中的頁面

SQL Server 維護自由頁面位址的鏈結清單，需緩衝區頁的工作執行緒可使用清單指向的第一個頁面。有機制負責將髒頁寫入磁碟、將久未引用頁面標記為自由頁面。

6.4 檢查點

檢查點程序週期性掃描快取並將特定資料庫的髒頁寫入磁碟，目的在將髒頁數量保持最小，縮短故障後復原時間。觸發情況：資料庫擁有者明確發出指令、記錄快滿達 70%（簡單復原模式）、預估復原時間過長、SQL Server 正常關閉。

6.5 記憶體組成與 AWE

SQL Server 記憶體由三部分組成：緩衝集區（最大、最常用）、大於 8 KB 的大記憶體區塊（單獨管理）、AWE 記憶體（僅資料快取頁面可用，需單獨追蹤）。可透過 sys.dm_os_memory_clerks 查詢非緩衝集區記憶體使用量。

6.6 調節緩衝集區大小與預讀

啟動時計算虛擬位址空間（VAS）大小。32 位元系統預設 2 GB（可透過 /3GB 開關擴至 3 GB），超過 3 GB 需啟用 AWE。SQL Server 2005 幾乎完全動態管理記憶體。預讀機制可預期資料與索引頁面需求，在實際需要前讀入緩衝集區，完全由系統內部管理。