三大工具對比
| 工具 | 版本 | 返回列關鍵信息 | 優勢 | 劣勢 |
|---|
sp_who | 所有 | spid, ecid, status, loginame, hostname, blk, dbname, cmd | 輕量、兼容性極佳 | 無 CPU/IO/等待/事務信息 |
sp_who2 | 2000+ | sp_who 全列 + CPUTime, DiskIO, LastBatch, ProgramName, SPID, RequestID | 顯示資源消耗、最後批次時間 | 非官方文檔、無等待類型 |
sys.dm_exec_requests | 2005+ | session_id, request_id, status, command, sql_handle, wait_type, wait_time, wait_resource, blocking_session_id, cpu_time, reads, writes, logical_reads, transaction_id, percent_complete | 完整等待鏈、進度、查詢文本關聯、可 JOIN 其他 DMV | 需 VIEW SERVER STATE 權限 |
核心列解讀與排查模式
1. 狀態碼
- `running`：正在執行；`runnable`：等待 CPU；`suspended`：等待資源 (I/O、鎖、鎖閂) — **重點關注**。
- `sleeping`：等待客戶端發送請求 (連接池閒置)；`background`：系統後台任務 (Checkpoint、Ghost Cleanup、Lazy Writer)。
2. 阻塞鏈定位 (blk / blocking_session_id)
- `blk = 0`：無阻塞；`blk > 0`：被該 SPID 阻塞；`blk = spid`：自我阻塞 (並行主線程等待子線程)。
- 遞歸查詢根阻塞者 (Head Blocker)：
-- 使用 DMV 遞歸 CTE 找出頭部阻塞會話
WITH BlockingChain AS (
SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource,
1 AS Level
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
UNION ALL
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource,
bc.Level + 1
FROM sys.dm_exec_requests r
JOIN BlockingChain bc ON r.session_id = bc.blocking_session_id
WHERE r.blocking_session_id <> 0
)
SELECT * FROM BlockingChain
ORDER BY Level DESC;
3. 等待類型 (wait_type) 速查
| wait_type | 含義 | 典型成因 | 對策方向 |
|---|
| LCK_M_* | 鎖等待 (S/X/U/IS/IX/SCH-M...) | 長事務、無索引掃描、死鎖 | 優化索引、縮短事務、RCSI |
| PAGEIOLATCH_* | 數據頁讀取 I/O | 冷數據、內存不足、惡意掃描 | 增加內存、覆蓋索引、分區 |
| CXPACKET | 並行協調等待 | 參數嗅探、統計過期、MAXDOP 不當 | OPTION (RECOMPILE)、調整 MAXDOP/CTFP |
| WRITELOG | 日誌寫入等待 | 日誌磁盤慢、大事務、延遲持久性關閉 | SSD/RAID10、分離日誌盤、延遲持久性 |
| THREADPOOL | 工作線程耗盡 | 連接風暴、長阻塞、MAX WORKER THREADS 過小 | 連接池優化、排查阻塞、調大配置 |
4. 關聯查詢文本與執行計劃
-- 獲取阻塞會話的完整 SQL 文本
SELECT r.session_id, r.status, r.wait_type, r.wait_time, r.blocking_session_id,
t.text AS FullBatchText,
SUBSTRING(t.text, r.statement_start_offset/2 + 1,
CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(t.text)
ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1 END) AS CurrentStatement,
qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id = OR r.blocking_session_id = ;
實戰場景速查手冊
| 現象 | 首要命令 | 關鍵觀察點 | 典型處置 |
|---|
| 應用突然全超時 | `sp_who2 active` / `SELECT * FROM sys.dm_exec_requests WHERE status='suspended'` | 大量 `LCK_M_X`、`blk` 指向同一 SPID | KILL 根阻塞 SPID (業務允許)、或優化其查詢/索引 |
| 單條查詢跑不完 | `sp_who2` 找 SPID → `DBCC INPUTBUFFER(SPID)` / DMV 取文本 | 高 `CPUTime`/`DiskIO`、 `CXPACKET`、`PAGEIOLATCH` | 看執行計劃、補索引、更新統計、加 Hint |
| 死鎖頻發 | `sys.dm_exec_requests` 看 `wait_type=LCK_M_*` 循環 | 死鎖圖 (System_Health XEvent / Trace Flag 1222) | 統一訪問順序、行級鎖升級、RCSI、重試邏輯 |
| TempDB 爭用 | `sp_who2` 看 `dbname=tempdb` 高 DiskIO | 臨時表/表變量/排序/版本存儲 | 多數據文件、優化查詢、避免大排序 |
權限最小化授予腳本
-- 僅授予查看動態管理視圖權限，無需 sysadmin
GRANT VIEW SERVER STATE TO [MonitorUser];
GRANT VIEW ANY DEFINITION TO [MonitorUser]; -- 可選，查看計劃/文本
總結
`sp_who/sp_who2` 適合**快速瞥見**；`sys.dm_exec_requests` + 關聯 DMV 是**深度診斷**標準。建議將核心 DMV 查詢封裝為監控視圖/存儲過程，集成到 Grafana/Prometheus/自研儀表盤，實現「秒級發現 → 分鐘級定位 → 自動化熔斷/告警」閉環。
