萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 異常診斷實戰手冊:CPU 飆高、阻塞死鎖、執行計劃分析的標準化排查流程

SQL Server 生產環境最常見的兩大故障類型:CPU 持續飆高 與 阻塞/死鎖。本文提供基於 DMV/DMF 的標準化排查腳本、系統存儲過程使用指南、DBCC 命令實戰、等待類型分析、長期監控體系建設,助你從「救火」轉向「預防」。

🔥 一、CPU 飆高標準化排查流程

1.1 核心診斷查詢:高 CPU 會話 + 執行 SQL 文本

WITH HighCPU AS (
    SELECT spid 
    FROM sys.sysprocesses 
    WHERE cpu > 500  -- 閾值視業務調整,單位:毫秒
)
SELECT DISTINCT 
    P.spid, P.kpid, P.blocked, P.waittype, P.waittime, P.lastwaittype,
    P.cpu, P.physical_io, P.memusage,
    P.loginame, P.hostname, P.program_name, P.dbid, P.cmd,
    T.text AS SQLText,
    QP.query_plan AS ExecutionPlan
FROM sys.sysprocesses P
CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) T
OUTER APPLY sys.dm_exec_query_plan(P.plan_handle) QP
JOIN HighCPU H ON H.spid = P.spid
WHERE P.spid > 50 -- 過濾系統進程
ORDER BY P.cpu DESC;

1.2 進階:查詢計劃緩存中高 CPU 查詢

SELECT TOP 20
    qs.total_worker_time / qs.execution_count AS AvgCPU,
    qs.total_worker_time AS TotalCPU,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS AvgDuration,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
        (CASE WHEN qs.statement_end_offset = -1 
              THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY AvgCPU DESC;

1.3 常見高 CPU 根因與對策

根因特徵對策
缺失索引 / 統計信息過期Table Scan / Index Scan、大量 Logical Reads補索引、UPDATE STATISTICS、強制計劃
參數嗅探同計劃參數差異大、間歇性慢OPTION (RECOMPILE) / OPTIMIZE FOR / 參數化強制
隱式轉換 / 列運算計劃中出現 CONVERT_IMPLICIT、Scalar Operator修正類型、移除列運算、計算列+索引
惡意標量函數UDF 在 SELECT/WHERE 中逐行執行內聯表值函數 / 應用層計算 / 計算列持久化
過度並行CXPACKET 等待高、MAXDOP 過大調整 MAXDOP、Cost Threshold、查詢 Hint

🔒 二、阻塞與死鎖標準化排查流程

2.1 實時阻塞鏈查詢

WITH BlockedChain AS (
    SELECT spid, blocked, 0 AS Level, CAST(spid AS VARCHAR(MAX)) AS Path
    FROM sys.sysprocesses WHERE blocked = 0 AND spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked > 0)
    UNION ALL
    SELECT P.spid, P.blocked, B.Level + 1, B.Path + ' -> ' + CAST(P.spid AS VARCHAR)
    FROM sys.sysprocesses P
    JOIN BlockedChain B ON P.blocked = B.spid
    WHERE P.blocked > 0
)
SELECT 
    BC.*, 
    P.loginame, P.hostname, P.program_name, P.dbid, P.cmd, P.waittype, P.lastwaittype,
    T.text AS SQLText,
    QP.query_plan AS Plan
FROM BlockedChain BC
JOIN sys.sysprocesses P ON P.spid = BC.spid
CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) T
OUTER APPLY sys.dm_exec_query_plan(P.plan_handle) QP
ORDER BY BC.Level, BC.spid;

2.2 系統存儲過程速查

命令用途關鍵輸出列
sp_who2 [active | 'login']快速查看活動會話、阻塞鏈SPID, Status, BlkBy, DBName, CPUTime, DiskIO, LastBatch, ProgramName
sp_lock [spid1] [spid2]查看會話持有/等待的鎖Type (TAB/PAG/RID/KEY), Mode (X/S/U/IX/SIX), Status (GRANT/WAIT/CNVRT)
DBCC INPUTBUFFER(spid)查看會話最後提交的語句EventType, EventInfo (前 255 字符)
sys.dm_tran_locks現代替代 sp_lock,資訊更全resource_type, request_mode, request_status, resource_description
sys.dm_os_waiting_tasks等待任務視圖,定位阻塞源頭wait_type, resource_address, blocking_session_id

2.3 死鎖捕獲與分析

  1. 擴展事件 (推薦):
    CREATE EVENT SESSION [DeadlockCapture] ON SERVER
    ADD EVENT sqlserver.xml_deadlock_report
    ADD TARGET package0.event_file (SET filename=N'DeadlockCapture.xel')
    WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS);
  2. 追蹤旗標 1222/1204 (舊版):DBCC TRACEON(1222, -1) 輸出到錯誤日志
  3. 分析關鍵:
    • victim vs survivor
    • keylock / pagelock / ridlock 類型
    • isolationlevel (ReadCommitted/RepeatableRead/Serializable)
    • 索引順序一致性 (按相同順序訪問表/索引)

2.4 死鎖預防設計模式

  • 統一訪問順序:所有事務按相同順序訪問表/索引
  • 縮短事務:極小事務範圍,避免用戶交互在事務中
  • 降低隔離級:READ COMMITTED SNAPSHOT (RCSI) 避免讀寫阻塞
  • 鎖升級控制:ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE/AUTO)
  • 應用層重試邏輯:捕獲 1205 錯誤碼,指數退避重試

📊 三、長期監控體系建設

  1. 基線建立:收集正常業務高峰期的 CPU、IO、等待統計、批次請求/秒、緩存命中率
  2. 關鍵 DMV 定時採集:sys.dm_os_performance_counters, sys.dm_os_wait_stats, sys.dm_exec_query_stats
  3. Query Store (SQL 2016+):開啟自動捕獲、計劃強制、回歸檢測
  4. 告警規則:
    • CPU > 80% 持續 5 分鐘
    • 阻塞鏈長度 > 5 或持續 > 30 秒
    • 死鎖發生 > 0/小時
    • PLE (Page Life Expectancy) < 300 秒
  5. 定期健檢**:索引碎片、統計信息年齡、TempDB 爭用、錯誤日志異常

排查心法:
不看「現象」看「等待」,不信「經驗」信「計劃」,
不改「參數」改「邏輯」,不治「標本」治「本質」。
建立標準化 SOP,讓初級 DBA 也能按圖索驥定位 80% 問題;
專家精力聚焦於架構優化、查詢重寫、體系建設——這才是 DBA 的核心價值。

copyright © 萬盛學電腦網 all rights reserved