SQL Server 生產環境最常見的兩大故障類型:CPU 持續飆高 與 阻塞/死鎖。本文提供基於 DMV/DMF 的標準化排查腳本、系統存儲過程使用指南、DBCC 命令實戰、等待類型分析、長期監控體系建設,助你從「救火」轉向「預防」。
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;
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;
| 根因 | 特徵 | 對策 |
|---|---|---|
| 缺失索引 / 統計信息過期 | 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 |
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;
| 命令 | 用途 | 關鍵輸出列 |
|---|---|---|
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 |
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);
DBCC TRACEON(1222, -1) 輸出到錯誤日志victim vs survivorkeylock / pagelock / ridlock 類型isolationlevel (ReadCommitted/RepeatableRead/Serializable)ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE/AUTO)sys.dm_os_performance_counters, sys.dm_os_wait_stats, sys.dm_exec_query_stats排查心法:
不看「現象」看「等待」,不信「經驗」信「計劃」,
不改「參數」改「邏輯」,不治「標本」治「本質」。
建立標準化 SOP,讓初級 DBA 也能按圖索驥定位 80% 問題;
專家精力聚焦於架構優化、查詢重寫、體系建設——這才是 DBA 的核心價值。