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

善用 DMV 全方位監控 SQL Server 運行狀態：CPU、記憶體、I/O、阻塞一網打盡

編輯：關於SqlServer

動態管理檢視（DMV）與動態管理函數（DMF）是 SQL Server 2005+ 內建的「X 光機」，無需安裝額外工具，即可透過 T-SQL 即時洞察伺服器內部狀態。本文彙整針對四大效能支柱——CPU、記憶體、I/O、阻塞——的實戰 DMV 查詢，助你快速定位瓶頸。

DMV 命名空間速查

前綴	領域	典型用途
`dm_db_*`	資料庫與物件	索引使用率、碎片度、檔案空間、資料庫鏡像狀態
`dm_exec_*`	查詢執行與連線	執行計畫、查詢統計、工作階段、請求、計畫快取
`dm_os_*`	作業系統層資源	排程器、記憶體 clerks、等待統計、閂鎖、記憶體通知
`dm_tran_*`	交易與鎖定	作用中交易、鎖定資源、版本存放區、快照隔離
`dm_io_*`	I/O 子系統	虛擬檔案統計、掛起 I/O 請求、I/O 延遲分析

一、CPU 瓶頸診斷三部曲

1. 找出累積 CPU 消耗最高的查詢群組

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

2. 連結 SQL 文本，直擊高耗 CPU 具體語句

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

3. 找出「單次執行平均 CPU」最高的語句（排除高頻低耗干擾）

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 * FROM sys.dm_exec_query_optimizer_info
WHERE counter IN ('optimizations', 'elapsed time');

-- 重編譯次數 > 1 的前 25 預存程序
SELECT TOP 25
    sql_text.text, qs.sql_handle, qs.plan_generation_num,
    qs.execution_count, qs.dbid, qs.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS sql_text
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;

5. 疑似高 CPU 操作符快速篩選

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

二、記憶體壓力檢測

-- 先啟用進階選項
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

-- 檢查關鍵記憶體設定
EXEC sp_configure 'awe_enabled';
EXEC sp_configure 'min server memory';
EXEC sp_configure 'max server memory';
EXEC sp_configure 'min memory per query';
EXEC sp_configure 'query wait';

-- 核心記憶體概覽（KB → MB）
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;

關鍵觀察： 若 bpool_committed_mb 長期接近 bpool_target_mb 且 max server memory 未達上限，表示記憶體受壓；若 Target > Committed 則系統尚有餘裕。

三、I/O 瓶頸精準定位

1. PAGEIOLATCH 等待統計（資料頁讀取等待）

SELECT wait_type, waiting_tasks_count, wait_time_ms,
       signal_wait_time_ms, wait_time_ms/waiting_tasks_count AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%' AND waiting_tasks_count > 0
ORDER BY wait_type;

wait_time_ms 與 waiting_tasks_count 顯著上升 = I/O 子系統跟不上。需對比基線判斷。

2. 即時掛起 I/O 請求（釐清具體檔案/磁碟）

SELECT
    database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS t1
JOIN sys.dm_io_pending_io_requests AS t2
    ON t1.file_handle = t2.io_handle;

正常情況應無回傳列。若有結果，立即檢查對應 database_id/file_id 所在磁碟狀態。

3. 邏輯/實體讀寫最高的查詢（優化目標）

SELECT TOP 5
    (total_logical_reads/execution_count) AS avg_logical_reads,
    (total_logical_writes/execution_count) AS avg_logical_writes,
    (total_physical_reads/execution_count) AS avg_physical_reads,
    execution_count, statement_start_offset, p.query_plan, q.text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) q
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC;

四、阻塞與等待分析

1. 即時阻塞鏈

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;

2. 取得阻塞者的 SQL 文本

DBCC INPUTBUFFER(<blocking_session_id>); -- 例：DBCC INPUTBUFFER(87)

3. 等待類型 TOP 10（排除良性等待）

SELECT TOP 10 *
FROM sys.dm_os_wait_stats
-- WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP',...)
ORDER BY wait_time_ms DESC;

4. 鎖定資源與阻塞關聯查詢（自訂存儲程序）

CREATE PROC dbo.sp_block @spid BIGINT = NULL
AS
SELECT
    t1.resource_type,
    DB_NAME(t1.resource_database_id) AS [database],
    t1.resource_associated_entity_id AS [blk_object],
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks AS t1
JOIN sys.dm_os_waiting_tasks AS t2
    ON t1.lock_owner_address = t2.resource_address
WHERE t1.request_session_id = ISNULL(@spid, t1.request_session_id);

使用方式：EXEC sp_block;（全部）或 EXEC sp_block @spid = 7;（特定 SPID）。

實務建議：建立 DMV 監控儀表板

將上述核心查詢封裝為檢視或預存程序。
SQL Agent 作業每 5-15 分鐘擷取一次，寫入歷史表（保留 30-90 天）。
搭配 Power BI / SSRS / Grafana 視覺化，設定閾值告警（如：PAGEIOLATCH 平均等待 > 20ms、CPU 佔用 > 85% 持續 10 分鐘）。
定期對比基線，異常趨勢早發現、早介入。

DMV 是零成本、高頻度、可程式化的監控利器。熟練掌握這組查詢，讓你在效能事件發生的「第一分鐘」就能鎖定兇手，而非在事後數小時才從日誌中拼湊線索。