您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 資料收集器與管理資料倉庫 (MDW) 完整架構解析

編輯：關於SqlServer

SQL Server 2008 引入 **資料收集器** 與 **管理資料倉庫 (MDW, Management Data Warehouse)**，提供內建、無代理、可擴展的效能監控與歷史趨勢分析平台。本文詳細解析其架構組件、三大系統收集集合、運作模式 (緩存/非緩存)、部署前置需求與價值定位。

一、核心架構組件

組件	角色	技術基礎
資料收集器	核心服務，協調收集、上傳、清除	SQL Agent Jobs + SSIS 套件
管理資料倉庫 (MDW)	關聯式資料庫，儲存收集的效能資料	使用者資料庫 (建議獨立實例/檔案群組)
收集類型	定義收集什麼資料、怎麼收集	T-SQL 查詢、SQL Trace、效能計數器、查詢活動
收集集合	邏輯分組：一組收集類型 + 排程 + 保留期	對應一或多個 SQL Agent 作業

二、四大內建收集類型

T-SQL 查詢：執行自訂查詢 (如 `sys.dm_os_wait_stats`)，結果上傳 MDW。
SQL Trace：啟動伺服器端追蹤 (Event/Column/Filter)，捕獲語句/等待/鎖定事件。
效能計數器：讀取 Windows Performance Counters (CPU、Memory、Disk、Network、SQL Server 特定計數器)。
查詢活動：蒐集查詢文本、執行計畫、執行統計 (類似 Query Store 前身)。

三、三大系統收集集合 (啟用 MDW 精靈後自動建立)

收集集合	收集內容	預設頻率	保留期	核心價值
磁碟使用狀況	每個資料庫的資料/記錄檔大小、可用空間、成長趨勢	每 6 小時	無限 (需手動清除)	容量規劃、預測磁碟耗盡時間、檔案成長模式分析
查詢統計資訊	高耗資源查詢文本、執行計畫、CPU/讀寫/持續時間、執行次數	每 15 分鐘 (可調)	14 天	識別 Top N 消耗查詢、計畫回歸偵測、索引缺失建議
伺服器活動狀況	等待統計、鎖定/阻塞、CPU/記憶體/磁碟 I/O、批次請求/秒、緩衝區命中率	每 60 秒 (快照)	14 天	即時效能基線、瓶頸根因分析 (CPU 壓力 vs I/O 壓力 vs 鎖爭用)

SSMS 管理節點下的資料收集器介面

圖一：SSMS 物件總管「管理」→「資料收集」節點

四、兩種收集模式：非緩存 vs 緩存

模式	觸發方式	資料流向	適用收集類型	延遲
非緩存	手動執行 / 排程 (SQL Agent Job)	SSIS 套件直接查詢來源 → 寫入 MDW	T-SQL 查詢、SQL Trace (較重)、效能計數器 (即時)	低 (排程間隔)
緩存	固定間隔 (如每 10 秒)	來源 → 本地快取目錄 → 定期批次上傳 MDW	查詢活動 (高頻、低延遲需求)	可控 (上傳間隔)

關鍵差異：緩存模式減少對來源系統即時查詢壓力，適合高頻收集 (查詢活動)；非緩存模式架構簡單，適合低頻、大量資料收集。

五、部署前置需求

✅ **SSIS 已安裝** (SQL Server 功能選項「Integration Services」)。
✅ **SQL Agent 服務啟動** (自動/手動均可，作業需 Agent 執行)。
✅ **MDW 目標資料庫實例已建立並可連線** (建議獨立實例，避免監控負擔影響被監控實例)。
✅ **足夠磁碟空間** 預估：每實例每天約 50-200MB (視收集頻率/保留期)。

六、價值定位與現代替代方案

SQL 2008-2014 核心監控平台**：無需第三方工具即可獲得企業級歷史效能資料。
SQL 2016+ Query Store**：取代「查詢統計資訊」收集集合，提供更細緻的計畫歷史、強制計畫、自動調優，且儲存於使用者資料庫內，無需額外 MDW。
SQL 2017+ 圖形化效能儀表板**：SSMS 內建報表直接讀取 DMV/Query Store/MDW。
現代雲端/容器環境**：Azure SQL Analytics、Prometheus + Grafana、Extended Events + Event Streaming 取代傳統 MDW 架構。

七、總結

資料收集器 + MDW 是 SQL Server 2008 重要里程碑，將分散的 DMV、追蹤、計數器資料**標準化、持久化、視覺化**。雖新版本有 Query Store 等更強工具，但 MDW 仍是跨版本、跨實例、長期趨勢分析 (特別是磁碟容量、伺服器整體負載) 的寶貴資產。掌握其架構有助於理解 SQL Server 監控體系演進脈絡。