SQL Server Analysis Services 效能調優實戰：善用 DMV 監控連線、記憶體、物件活動

前置準備

• 工具：SSMS → 新增查詢 → Analysis Services MDX 查詢
• 連接目標 SSAS 執行個體，選擇 Adventure Works OLAP 資料庫

1. 探索可用 DMV

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES
ORDER BY TABLE_NAME

關注 DISCOVER_ 開頭的資料表，如 DISCOVER_CONNECTIONS、DISCOVER_SESSIONS、DISCOVER_OBJECT_MEMORY_USAGE、DISCOVER_OBJECT_ACTIVITY。

2. 監控目前連線

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

• CONNECTION_HOST_APPLICATION：識別用戶端應用程式 (SSMS、Excel、自訂 App)
• CONNECTION_LAST_COMMAND_*：找出長時間執行的命令

3. 實驗：Excel 連接前後對比

1. 關閉 Excel 開啟的 Cube 瀏覽視窗
2. 重跑 DISCOVER_CONNECTIONS，SSMS 連線消失
3. 用 Excel 2007 連接 SSAS (資料 → 來自其他來源 → Analysis Services)，建立樞紐分析表
4. 再次查詢，觀察新增的 Excel 連線

4. 深入 Session 細節

SELECT
    session_connection_id, session_spid, session_user_name,
    session_last_command, session_start_time, session_cpu_time_ms,
    session_reads, session_writes, session_status,
    session_current_database, session_used_memory,
    session_elapsed_time_ms, session_last_command_start_time,
    session_last_command_end_time
FROM $SYSTEM.DISCOVER_SESSIONS

• session_status = 1 表示有作用中查詢
• session_cpu_time_ms、session_reads/writes 找出資源消耗大戶

5. 物件層級記憶體與活動

-- 每個物件佔用記憶體
SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE

-- 每個物件的存取頻率/最後存取時間
SELECT * FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY

配合記憶體壓力調整快取策略、分割熱門/冷門物件。

6. 自動化建議

• 撰寫 PowerShell / SSIS 定期收集上述 DMV，寫入關聯式資料庫建立歷史趨勢
• 搭配 SSRS 報表視覺化：連線數趨勢、記憶體使用熱力圖、Top N 耗資源 Session
• 設定閾值警示 (如連線數 > 500、記憶體使用 > 85%) 發送郵件通知 DBA