SQL Server 運行狀態監控實戰 (一)：DMV 架構與 CPU 瓶頸核心診斷查詢

動態管理視圖 (DMV) 分類總覽

SQL Server 2005+ 提供 DMV/DMF 監控伺服器狀態，主要分類：

查詢所有 DMV/DMF 名稱

SELECT * FROM sys.system_objects 
WHERE name LIKE 'dm_%' ORDER BY name;

CPU 瓶頸核心診斷查詢集

1. 總 CPU 消耗最高的 50 個查詢計畫

SELECT TOP 50
    SUM(qs.total_worker_time) AS total_cpu_time,
    SUM(qs.execution_count) AS total_execution_count,
    COUNT(*) AS number_of_statements,
    qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC;

2. 含 SQL 文本的 CPU 總消耗排行

SELECT 
    total_cpu_time, 
    total_execution_count, 
    number_of_statements, 
    s2.text
FROM (
    SELECT TOP 50
        SUM(qs.total_worker_time) AS total_cpu_time,
        SUM(qs.execution_count) AS total_execution_count,
        COUNT(*) AS number_of_statements,
        qs.sql_handle
    FROM sys.dm_exec_query_stats AS qs
    GROUP BY qs.sql_handle
    ORDER BY SUM(qs.total_worker_time) DESC
) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2;

3. 平均 CPU 消耗最高的 50 條 SQL 陳述式

SELECT TOP 50
    total_worker_time/execution_count AS [Avg CPU Time],
    (SELECT SUBSTRING(text, statement_start_offset/2,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), text)) * 2 
            ELSE statement_end_offset END) - statement_start_offset)/2
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
    *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC;

4. 找出最高 CPU 累計消耗的查詢計畫詳細資訊

SELECT 
    highest_cpu_queries.plan_handle, 
    highest_cpu_queries.total_worker_time, 
    q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (
    SELECT TOP 50 qs.plan_handle, qs.total_worker_time
    FROM sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time DESC
) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;

5. 尋找高 CPU 運算子的可疑查詢計畫 (Hash Match、Sort)

SELECT * FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE '%Sort%'
   OR CAST(query_plan AS NVARCHAR(MAX)) LIKE '%Hash Match%';

效能低落查詢計畫處理流程

1. 識別高 CPU 查詢 (上述查詢)
2. 對涉及資料表執行 UPDATE STATISTICS
3. 觀察問題是否解決
4. 若持續存在，收集資料回報支援團隊