您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005/2008 運行狀態監控：核心 DMV 查詢實戰指南

編輯：關於SqlServer

動態管理視圖（DMV）體系

SQL Server 2005 引入 DMV/DMF，提供服務器內部狀態的實時可見性，分為五大類：

dm_db_*：數據庫與對象狀態（文件、索引、統計信息）
dm_exec_*：查詢執行、緩存計劃、會話連接
dm_os_*：作業系統級資源（內存、CPU、鎖、調度器）
dm_tran_*：事務、鎖、版本存儲、隔離級別
dm_io_*：磁盤 I/O、網絡 I/O、文件讀寫延遲

高頻監控查詢集錦

1. CPU 消耗最高的 Top 50 查詢

SELECT TOP 50
    qs.total_worker_time / qs.execution_count AS [Avg CPU Time (μs)],
    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,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time],
    qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads],
    qs.creation_time,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY [Avg CPU Time (μs)] DESC;

2. 發現高 CPU 運算符的計劃（Hash Match、Sort）

SELECT 
    cp.objtype,
    cp.usecounts,
    cp.size_in_bytes,
    st.text,
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%Hash Match%'
   OR CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%Sort%';

這些運算符通常意味著缺少索引、統計信息過期或查詢寫法不當。

3. 服務器硬件與內存資源概覽

SELECT 
    cpu_count,
    hyperthread_ratio,
    scheduler_count,
    physical_memory_in_bytes / 1024 / 1024 AS physical_memory_mb,
    virtual_memory_in_bytes / 1024 / 1024 AS virtual_memory_mb,
    bpool_committed * 8 / 1024 AS bpool_committed_mb,
    bpool_commit_target * 8 / 1024 AS bpool_target_mb,
    bpool_visible * 8 / 1024 AS bpool_visible_mb
FROM sys.dm_os_sys_info;

4. 重新編譯最頻繁的 Top 25 存儲過程

SELECT TOP 25
    st.text,
    qs.sql_handle,
    qs.plan_generation_num AS recompile_count,
    qs.execution_count,
    DB_NAME(st.dbid) AS database_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;

plan_generation_num 表示計劃重新編譯次數，頻繁重編譯消耗 CPU 並導致計劃緩存抖動。

5. I/O 消耗最大的 Top 5 查詢

SELECT TOP 5
    (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS avg_io_per_exec,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_logical_writes / qs.execution_count AS avg_logical_writes,
    qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
    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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_io_per_exec DESC;