不再依赖旧版系统表，使用DMV和扩展事件精准定位死锁
很多DBA还在用sysprocesses和dbcc inputbuffer处理死锁，但在SQL Server 2012+环境中，我们有更强大的工具。今天带你全面升级死锁处理方案。
一、为什么旧方法该淘汰了？
传统方法（如系统表sysprocesses）存在明显缺陷：
SQL Server 2012+ 提供了更专业的解决方案
二、快速诊断：当前阻塞和死锁查询
2.1 查看当前阻塞关系
SELECT blocked.session_id AS 被阻塞SPID, blocking.session_id AS 阻塞SPID, blocked.wait_type AS 等待类型, blocked.wait_time AS 等待时间毫秒, blocked.wait_resource AS 等待资源, blocking.login_name AS 阻塞者登录名, blocking.program_name AS 阻塞者程序, blocking.host_name AS 阻塞者主机, blocked.text AS 被阻塞SQL, blocking.text AS 阻塞SQLFROM sys.dm_exec_requests blockedJOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_idCROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blockedCROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blockingWHERE blocked.blocking_session_id > 0
2.2 检测当前死锁（实时）
SELECT deathtime AS 死锁时间, session_id AS 牺牲品SPID, login_name AS 登录名, program_name AS 程序名, host_name AS 主机名, command AS 最后命令, text AS 执行SQLFROM sys.traces tCROSS APPLY fn_trace_geteventinfo(t.id) eCROSS APPLY sys.dm_exec_sql_text(( SELECT TOP 1 text FROM fn_trace_gettable(t.path, DEFAULT) WHERE eventclass = 122 ORDER BY starttime DESC)) sqltextWHERE e.eventid = 122
三、实战存储过程：自动Kill死锁进程
下面是一个基于DMV的现代化解决方案：
CREATE PROCEDURE usp_KillDeadlockProcess @AutoKill BIT = 1, @ShowNormalIfNoDeadlock BIT = 0 ASBEGIN SET NOCOUNT ON IF OBJECT_ID('tempdb..#DeadlockProcess') IS NOT NULL DROP TABLE #DeadlockProcess ;WITH DeadlockChain AS ( SELECT session_id, blocking_session_id, wait_type, wait_time, login_name, program_name, host_name, status, command, cpu_time, total_elapsed_time, open_transaction_count, text, query_hash, 1 AS level FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE blocking_session_id > 0 UNION ALL SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.login_name, r.program_name, r.host_name, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.open_transaction_count, sqltext.text, r.query_hash, dc.level + 1 FROM sys.dm_exec_requests r INNER JOIN DeadlockChain dc ON r.session_id = dc.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) sqltext WHERE r.session_id <> dc.session_id ) SELECT session_id AS 进程ID, blocking_session_id AS 阻塞者ID, login_name AS 登录名, program_name AS 应用程序, host_name AS 工作站, status AS 状态, command AS 命令, cpu_time AS CPU毫秒, total_elapsed_time AS 已执行毫秒, open_transaction_count AS 打开事务数, text AS 执行SQL, CASE WHEN session_id IN ( SELECT session_id FROM DeadlockChain WHERE session_id IN (SELECT blocking_session_id FROM DeadlockChain) AND blocking_session_id IN (SELECT session_id FROM DeadlockChain) ) THEN '死锁进程' ELSE '被阻塞进程' END AS 标志, ROW_NUMBER() OVER (ORDER BY session_id) AS RowNum INTO #DeadlockProcess FROM DeadlockChain WHERE EXISTS ( SELECT 1 FROM DeadlockChain dc2 WHERE dc2.session_id = DeadlockChain.blocking_session_id AND dc2.blocking_session_id = DeadlockChain.session_id ) IF @@ROWCOUNT = 0 BEGIN IF @ShowNormalIfNoDeadlock = 1 BEGIN SELECT TOP 20 session_id AS 进程ID, login_name AS 登录名, program_name AS 应用程序, status AS 状态, cpu_time AS CPU毫秒, total_elapsed_time AS 执行时间毫秒, open_transaction_count AS 打开事务数, text AS 最后执行的SQL FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE session_id > 50 ORDER BY cpu_time DESC END ELSE BEGIN PRINT '当前没有检测到死锁进程' END RETURN END SELECT * FROM #DeadlockProcess IF @AutoKill = 1 BEGIN DECLARE @spid INT, @sql NVARCHAR(100) DECLARE kill_cursor CURSOR FOR SELECT 进程ID FROM #DeadlockProcess WHERE 标志 = '死锁进程' OPEN kill_cursor FETCH NEXT FROM kill_cursor INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.DeadlockLog (KillTime, SPID, LoginName, ProgramName, SQLText) SELECT GETDATE(), 进程ID, 登录名, 应用程序, 执行SQL FROM #DeadlockProcess WHERE 进程ID = @spid SET @sql = 'KILL ' + CAST(@spid AS VARCHAR) BEGIN TRY EXEC sp_executesql @sql PRINT '已杀死死锁进程: ' + CAST(@spid AS VARCHAR) END TRY BEGIN CATCH PRINT '杀死进程失败: ' + CAST(@spid AS VARCHAR) + ', 错误:' + ERROR_MESSAGE() END CATCH FETCH NEXT FROM kill_cursor INTO @spid END CLOSE kill_cursor DEALLOCATE kill_cursor ENDENDGO
使用示例
EXEC usp_KillDeadlockProcess @AutoKill = 0, @ShowNormalIfNoDeadlock = 0EXEC usp_KillDeadlockProcess @AutoKill = 1EXEC usp_KillDeadlockProcess @AutoKill = 0, @ShowNormalIfNoDeadlock = 1
四、进阶方案：扩展事件（Extended Events）
4.1 创建死锁捕获扩展事件
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER ADD EVENT sqlserver.xml_deadlock_report( ACTION(sqlserver.sql_text, sqlserver.session_id, sqlserver.username)),ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.sql_text, sqlserver.session_id))ADD TARGET package0.event_file(SET filename = N'C:\XELogs\DeadlockMonitor.xel')WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)GOALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START
4.2 读取死锁XML报告
SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name, event_data.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time, event_data.query('(event/data/value/deadlock)[1]') AS deadlock_graphFROM ( SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file( N'C:\XELogs\DeadlockMonitor*.xel', NULL, NULL, NULL )) AS edWHERE event_data.value('(event/@name)[1]', 'varchar(50)') = 'xml_deadlock_report'ORDER BY deadlock_time DESC
五、死锁分析利器：系统函数
5.1 获取进程完整SQL
SELECT session_id, text AS 完整SQL, statement_start_offset, statement_end_offset, SUBSTRING( text, (statement_start_offset/2) + 1, (CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2 ) AS 当前执行语句FROM sys.dm_exec_requestsCROSS APPLY sys.dm_exec_sql_text(sql_handle)
5.2 查看锁详细信息
SELECT request_session_id AS SPID, resource_type AS 资源类型, resource_database_id AS 数据库ID, resource_description AS 资源描述, request_mode AS 锁模式, request_status AS 锁状态, request_owner_type AS 所有者类型FROM sys.dm_tran_locksWHERE resource_database_id = DB_ID()
六、预防死锁的最佳实践
6.1 设置合理的锁超时
SET LOCK_TIMEOUT 30000 @@LOCK_TIMEOUT
6.2 启用快照隔离（降低死锁）
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ONALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON
6.3 监控死锁频率
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_msFROM sys.dm_os_wait_statsWHERE wait_type LIKE '%DEADLOCK%'
七、总结与对比
建议：如果你的SQL Server版本≥2012，强烈建议迁移到新的DMV和扩展事件方案，不仅性能更好，还能获得更多诊断信息。
📌 小贴士：将上述存储过程部署到你的监控数据库中，配合SQL Agent作业每小时执行一次，自动记录和清理死锁，让你的数据库运行更稳定。
阅读原文：原文链接
该文章在 2026/4/27 14:53:54 编辑过
400 186 1886
