SQL Server 運行狀態監控實戰 (二)：記憶體、I/O、阻塞與編譯重編譯診斷

編譯與重新編譯監控

過度編譯/重新編譯會導致 CPU 壓力，透過 DMV 追蹤：

-- 查詢優化器資訊
SELECT * FROM sys.dm_exec_query_optimizer_info 
WHERE counter = 'optimizations' OR counter = 'elapsed time';

-- 找出重新編譯次數最多的前 25 個存儲過程
SELECT TOP 25 
    sql_text.text, 
    sql_handle, 
    plan_generation_num,  -- 重新編譯次數
    execution_count, 
    dbid, 
    objectid
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC;

記憶體瓶頸診斷

啟用進階選項

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';

系統記憶體與緩衝區資訊

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;

I/O 瓶頸分析

閂鎖等待統計

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;

掛起的 I/O 請求檢測

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

高 I/O 查詢識別

-- 平均邏輯讀寫最高的查詢
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;

阻塞診斷與解決

尋找阻塞會話

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

取得阻塞者的 SQL 陳述式

DBCC INPUTBUFFER(87); -- 替換實際的 blocking_session_id

等待統計分析 (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;

自訂阻塞報表存儲過程

CREATE PROC dbo.sp_block (@spid BIGINT = NULL)
AS
SELECT 
    t1.resource_type,
    'database' = DB_NAME(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks t1
JOIN sys.dm_os_waiting_tasks 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;