SQL Server 2008 資料收集器與管理資料倉庫（MDW）架構解析

前言

SQL Server 2008 引入資料收集器與管理資料倉庫，提供內建、可擴充的效能監控與歷史趨勢分析平台。透過 SSIS 封裝與 SQL Agent 作業自動化收集、上傳、淨化、保留，解決傳統手工撰寫監控腳本的維護困境。

架構總覽

+------------------+     SSIS Packages      +---------------------+
|  Data Collector  |  <------------------->  |  Management Data    |
|  (Collection)    |   Upload/Schedule      |  Warehouse (MDW)    |
+------------------+                        +---------------------+
        ^                                           ^
        | SQL Agent Jobs                            | Relational DB
        | (Schedule/Upload)                         | (Stores History)
        v                                           v
+------------------+                        +---------------------+
|  Target Instance |                        |  Reporting /        |
|  (Source Metrics)|                        |  Analysis Tools     |
+------------------+                        +---------------------+

核心組件

1. 資料收集器類型

2. 快取模式差異

• 無快取： 手動/排程觸發 → 即時收集 → 即時上傳 MDW
• 有快取： 背景持續收集 → 本地快取目錄 → 定期批次上傳 MDW（低影響、高頻率）

三大系統預設收集組

1. 磁碟使用狀況

• 收集：每資料庫資料/記錄檔大小、可用空間、成長趨勢
• 頻率：預設每 6 小時
• 用途：容量規劃、檔案成長預警、儲存成本分析

2. 查詢統計資訊

• 收集：高耗資源查詢文本、執行計畫、執行統計（CPU、Duration、Reads、Writes）
• 來源：sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_query_plan
• 用途：識別 Top N 慢查詢、計畫變更追蹤、索引缺失分析

3. 伺服器活動狀況

• 收集：系統等待統計、CPU/記憶體/磁碟/網路計數器、封鎖/死結資訊
• 來源：sys.dm_os_wait_stats, sys.dm_os_performance_counters, sys.dm_exec_requests
• 用途：瓶頸識別、基準線建立、異常偵測

啟用與配置步驟

1. SSMS → Management → Data Collection 右鍵 Configure Management Data Warehouse
2. 精靈步驟：
   1. 建立/選擇 MDW 資料庫（新建或現有）
   2. 設定資料收集器上傳帳號（需 MDW db_datareader/writer）
   3. 選擇要監控的目標執行個體（可多個）
   4. 啟用三大系統收集組（可自訂頻率、保留天數）
3. 完成後：SQL Agent 建立對應收集/上傳/清除作業

MDW 結構描述核心資料表

報表與分析

• SSMS → Management → Data Collection → 右鍵收集組 → Reports
• 內建報表：磁碟使用趨勢、查詢統計歷史、伺服器活動時間序列
• 自訂報表：查詢 MDW 資料表，整合 SSRS/Power BI

擴充：自訂收集組

-- 範例：收集特定資料表大小趨勢
-- 1. SSMS → Data Collection → 右鍵 New → Custom Collection Set
-- 2. 類型：Generic T-SQL Query Collector
-- 3. 查詢：
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    p.index_id,
    SUM(a.total_pages) * 8.0 / 1024 AS SizeMB
FROM sys.partitions p
JOIN sys.allocation_units a ON p.hobt_id = a.container_id
WHERE p.index_id IN (0,1)  -- Heap + Clustered
GROUP BY p.object_id, p.index_id
-- 4. 設定排程、快取模式、保留期

前置需求與限制

結語

資料收集器與 MDW 將「監控腳本開發+排程+歷史儲存+報表」標準化、產品化。啟用三大系統收集組即可獲得零開發成本的效能基準線與趨勢分析；自訂收集組滿足業務特有指標。配合 SSRS/Power BI 視覺化，構建從「被動救火」到「主動治理」的資料庫營運體系。