SQL Server 2008 Profiler 效能分析工具完整指南：事件選擇、資料行、篩選、模板、自動化與最佳實務

SQL Server Profiler 是圖形化追蹤與分析工具，支援監控查詢、收集效能資料、診斷死鎖、調試 T-SQL、重播負載。本文系統介紹 Profiler 核心概念 (追蹤、事件、資料行、篩選、模板)、自動化腳本化追蹤 (sp_trace_* 預存程序)、結合系統效能監視器分析、使用最佳實務 (降低負載) 以及無 Profiler 環境下使用 DMV (sys.dm_exec_query_stats) 快速定位效能問題。

一、Profiler 核心概念與操作

1. 啟動與建立追蹤

• 路徑：開始 → 所有程式 → Microsoft SQL Server 2008 → 效能工具 → SQL Server Profiler
• 或 SSMS → 工具 → SQL Server Profiler
• 檔案 → 新增 → 追蹤，連線目標執行個體，開啟「追蹤屬性」對話盒。

2. 事件選擇 (Events Selection)

點選 事件選擇 頁籤，勾選需捕捉的事件類別與事件。常用效能分析事件：

3. 資料行 (Data Columns)

每個事件的屬性欄位。常用欄位：

可點擊 組織資料行 調整顯示順序與分組。

4. 資料行篩選器 (Column Filters)

在捕捉階段即過濾，大幅降低負載與輸出量。常用篩選：

5. 追蹤模板

• 定義好事件/資料行/篩選後，檔案 → 另存為 → 追蹤模板，命名儲存。
• 下次新建追蹤時直接選用模板，確保一致性。

6. 追蹤資料儲存與分析

• 執行追蹤 → 工具列暫停/停止/重新啟動。
• 完成後：檔案 → 另存為 → 追蹤檔案 (原生 .trc) 或 追蹤資料表 (寫入資料表，可用 T-SQL 查詢分析)。

二、追蹤自動化：腳本化與預存程序 (無 GUI 負載)

GUI 追蹤會透過網路回傳事件至用戶端緩衝區，造成伺服器額外負載。生產環境建議使用系統預存程序建立伺服器端追蹤：

核心預存程序序列

1. sp_trace_create：建立追蹤定義，回傳 traceid。
2. sp_trace_setevent：加入事件與資料行。
3. sp_trace_setfilter：套用篩選條件。
4. sp_trace_setstatus @traceid, 1：啟動追蹤 (背景運行，不需保持 SSMS 連線)。

管理追蹤

-- 查看所有作用中追蹤
SELECT * FROM ::fn_trace_getinfo(default);
-- 停止追蹤 (traceid=1)
EXEC sp_trace_setstatus 1, 0;
-- 關閉並移除定義
EXEC sp_trace_setstatus 1, 2;

產生的 .trc 檔案可用 Profiler 開啟分析，或用 fn_trace_gettable 讀入資料表查詢。

從 GUI 匯出腳本

1. 在 Profiler 定義好追蹤。
2. 檔案 → 匯出 → 腳本追蹤定義，選目標版本 (SQL Server 2005/2008)。
3. 儲存 .sql 檔，用 SQL Agent 排程或 sqlcmd 執行。

三、結合系統效能監視器

若同步擷取 PerfMon (.blg) 與 Profiler (.trc) 且時間範圍重疊：

1. Profiler 開啟 .trc 檔。
2. 檔案 → 匯入效能資料 選擇 .blg 檔。
3. 勾選欲疊加的計數器 (CPU%、Disk Queue Length、Batch Requests/sec 等)。
4. 上方面板選取時間點，下方 PerfMon 圖表會顯示紅線對應；反之亦然。

可快速將「系統資源壓力點」對應到「具體高耗查詢」。

四、Profiler 使用最佳實務 (降低負載)

1. 限制事件與資料行數量：只捕捉必要事件 (完成類事件)，資料行不增加額外負載。
2. 捨棄啟動類事件 (SP:StmtStarting, SQL:StmtStarting) — 效能指標 (CPU/Reads/Duration) 僅在完成事件可得。
3. 限制輸出大小：設定最大檔案大小/資料列數，或用篩選器 (Duration >= 2ms, Reads >= 100)。
4. 避免線上資料行排序/分組：捕捉完成後另存 .trc，離線開啟再排序。
5. 遠端執行 Profiler：不要在生產伺服器本機或終端機階段運行 GUI；將 .trc 儲存於本機磁碟。最佳做法仍是伺服器端預存程序追蹤。
6. 謹慎使用 Showplan XML 類事件：極大幅增加負載，嚴禁生產環境使用。

五、無 Profiler 環境：用 DMV 快速定位頂尖耗資源查詢

sys.dm_exec_query_stats 保存快取計畫的彙總執行統計，即時查詢無需追蹤：

-- 依總 CPU 時間找前 20 高耗查詢
SELECT TOP 20
    qs.total_worker_time / qs.execution_count AS avg_cpu_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

常用欄位：

搭配 sys.dm_exec_sql_text、sys.dm_exec_query_plan 即可取得完整查詢文字與執行計畫。

總結

• Profiler 適合**臨時診斷、死鎖重現、開發測試**；生產環境長期監控請用**伺服器端追蹤 (sp_trace_*)** 或 **擴充事件 (Extended Events, SQL 2008+)**。
• 掌握事件/資料行/篩選/模板/自動化/PerfMon 聯動/最佳實務，可精準定位效能瓶頸而不拖垮系統。
• DMV 提供零負載的即時查詢統計視角，是日常效能巡檢的首選工具。