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

SQL Server 異常處理實戰：CPU 飆高與阻塞排查標準流程

編輯：關於SqlServer

核心原則

SQL Server 線上事故 90% 源自查詢問題，癥狀集中於 CPU 持續飆高 與 阻塞/死鎖。建立標準化排查腳本，將 MTTR (平均修復時間) 從小時級壓縮至分鐘級。

一、CPU 飆高排查標準流程

步驟 1：鎖定高 CPU 會話 (sys.dm_exec_requests + sys.dm_exec_sql_text)

WITH HighCpuSessions AS (
  SELECT session_id, cpu_time, total_elapsed_time, 
         sql_handle, statement_start_offset, statement_end_offset
  FROM sys.dm_exec_requests
  WHERE cpu_time > 5000  -- 超過 5 秒 CPU 積累
    AND status IN ('running','runnable','suspended')
)
SELECT 
  H.session_id,
  H.cpu_time,
  H.total_elapsed_time,
  DB_NAME(DB_ID()) AS CurrentDB,
  SUBSTRING(ST.text, 
    (H.statement_start_offset/2)+1,
    CASE WHEN H.statement_end_offset = -1 THEN DATALENGTH(ST.text)
         ELSE (H.statement_end_offset - H.statement_start_offset)/2 + 1 END
  ) AS CurrentStatement,
  ST.text AS FullBatchText
FROM HighCpuSessions H
CROSS APPLY sys.dm_exec_sql_text(H.sql_handle) ST
ORDER BY H.cpu_time DESC;

步驟 2：分析執行計畫

SSMS 顯示估計/實際執行計畫 (Ctrl+M / Ctrl+L)。
關注：Table Scan、Index Scan (非覆蓋)、Key Lookup (大量)、Sort (溢寫 Tempdb)、Parallelism (不平衡/過度)。
收集 SET STATISTICS IO, TIME ON 實際 I/O 與時間。

步驟 3：快速緩解

KILL 會話 (業務允許時) 釋放資源。
更新統計資訊：UPDATE STATISTICS TableName WITH FULLSCAN。
加索引/改寫查詢：覆蓋索引消除 Key Lookup、SARGable 改寫、避免函數包裹欄位。
調整 MAXDOP：會話層級 OPTION (MAXDOP 1) 或實例層級降低平行度。

二、阻塞/死鎖排查標準流程

步驟 1：識別阻塞鏈 (sys.dm_exec_requests + sys.dm_tran_locks)

WITH BlockingChain AS (
  SELECT 
    r.session_id AS Spid,
    r.blocking_session_id AS BlockedBy,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,
    r.sql_handle
  FROM sys.dm_exec_requests r
  WHERE r.blocking_session_id > 0
     OR r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0)
)
SELECT 
  B.Spid,
  B.BlockedBy,
  B.wait_type,
  B.wait_resource,
  SUBSTRING(ST.text, (B.statement_start_offset/2)+1,
    CASE WHEN B.statement_end_offset=-1 THEN DATALENGTH(ST.text)
         ELSE (B.statement_end_offset-B.statement_start_offset)/2+1 END) AS CurrentStmt,
  ST.text AS FullBatch
FROM BlockingChain B
CROSS APPLY sys.dm_exec_sql_text(B.sql_handle) ST
ORDER BY B.BlockedBy, B.Spid;

步驟 2：輔助系統預存程序

EXEC sp_who2 'active';          -- 快速概覽 CPU、DiskIO、LastBatch、ProgramName
EXEC sp_lock;                   -- 鎖資源詳細 (ObjId, IndId, Type, Resource, Mode, Status)
-- 鎖資源對應物件
SELECT OBJECT_NAME(ObjId) AS ObjectName, * FROM ##sp_lock_result;  -- 需先 INSERT EXEC

-- 關鍵：DBCC INPUTBUFFER 看最後一條客戶端指令
DBCC INPUTBUFFER(52);  -- SPID

步驟 3：死鎖圖分析 (系統健康擴充事件 / Trace Flag 1222)

system_health Extended Events 預設擷取 xml_deadlock_report。
查詢：SELECT CAST(target_data AS XML) FROM sys.dm_xe_session_targets ...。
或啟用 DBCC TRACEON(1222, -1) 寫入錯誤日誌 (舊版)。

步驟 4：快速緩解

識別 Head Blocker (阻塞鏈頂端) → KILL (業務確認後)。
檢查隔離等級：READ COMMITTED SNAPSHOT (RCSI) / SNAPSHOT 減少讀寫阻塞。
縮短交易範圍、調整索引減少鎖升級、避免長交易中跨資料庫/連結伺服器呼叫。

三、常用 DMV 快速查詢清單

目的	查詢
等待統計	`SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;`
索引使用率	`SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID();`
缺失索引建議	`SELECT * FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID();`
查詢計畫快取	`SELECT TOP 50 qs.total_worker_time/qs.execution_count AS AvgCPU, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY AvgCPU DESC;`
Tempdb 空間耗用	`SELECT * FROM sys.dm_db_file_space_usage WHERE database_id = 2;`