SQL Server 2008 效能不佳查詢排查：DMV 監控腳本實戰

透過動態管理檢視（DMV）快速定位高耗資源、高頻執行的問題查詢，是 DBA 日常調優的核心技能。

核心監控查詢

SELECT 
    creation_time AS '語句編譯時間',
    last_execution_time AS '上次執行時間',
    total_physical_reads AS '物理讀取總次數',
    total_logical_reads/execution_count AS '每次邏輯讀次數',
    total_logical_reads AS '邏輯讀取總次數',
    total_logical_writes AS '邏輯寫入總次數',
    execution_count AS '執行次數',
    total_worker_time/1000 AS '所用CPU總時間(ms)',
    total_elapsed_time/1000 AS '總花費時間(ms)',
    (total_elapsed_time / execution_count)/1000 AS '平均時間(ms)',
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS '執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
    AND qs.execution_count > 10000
    AND (total_elapsed_time / execution_count)/1000 > 150
ORDER BY total_elapsed_time / execution_count DESC;

關鍵指標解讀

• execution_count > 10000：高頻執行查詢，微小效能差異會被放大。
• 平均時間 > 150ms：單次執行耗時過長，需優化。
• total_logical_reads/execution_count：平均邏輯讀，反映索引覆蓋與查詢計畫品質。
• 排除 fetch 關鍵字，過濾游標操作干擾。

定期執行此腳本並記錄基準線，可快速發現效能退化趨勢。