--1.通过以下dmvs方式
SELECT dowt.session_id ,dowt.wait_duration_ms ,dowt.wait_type ,dowt.blocking_session_id ,dese.host_name as HostName ,der.command ,der.percent_complete ,der.cpu_time ,der.total_elapsed_time ,der.reads ,der.writes ,der.logical_reads ,der.row_count ,dest.text AS QueryText ,dest.dbid AS DatabaseID ,deqp.query_plan ,der.plan_handle FROM sys.dm_os_waiting_tasks as dowt INNER JOIN sys.dm_exec_sessions as dese ON dowt.session_id = dese.session_id INNER JOIN sys.dm_exec_requests as der ON dese.session_id = der.session_id CROSS APPLY sys.dm_exec_sql_text(der.plan_handle) as dest CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) as deqp WHERE dowt.session_id > 50
--2.通过sysprocesses查看死锁
--查询死锁脚本
USE master GO DECLARE @spid INT,@bl INT DECLARE s_cur CURSOR FOR SELECT 0,blocked FROM ( SELECT * FROM sysprocesses WHERE blocked > 0 ) a WHERE NOT EXISTS ( SELECT * FROM ( SELECT * FROM sysprocesses WHERE blocked > 0 ) b WHERE a.blocked= spid ) UNION SELECT spid,blocked FROM sysprocesses WHERE blocked > 0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 BEGIN IF @spid = 0 SELECT ' 引起数据库死锁的是: ' + CAST ( @bl AS VARCHAR ( 10 ) ) + ' 进程号, 其执行的SQL 语法如下' ELSE SELECT ' 进程号SPID :' + CAST ( @spid AS VARCHAR ( 10 ) ) + ' 被' + ' 进程号SPID :' + CAST ( @bl AS VARCHAR ( 10 ) ) + ' 阻塞, 其当前进程执行的SQL 语法如下' DBCC INPUTBUFFER ( @bl ) FETCH NEXT FROM s_cur INTO @spid,@bl END CLOSE s_cur DEALLOCATE s_cur EXEC sp_who2
--杀死锁脚本:
SELECT * FROM master.dbo.sysprocesses WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0 AND status = 'sleeping' AND last_batch < dateadd( MINUTE, - 10, getdate( ) ) AND login_time < dateadd( MINUTE, - 10, getdate( ) ) DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill ' + rtrim( spid ) FROM master.dbo.sysprocesses WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0 AND status = 'sleeping' AND last_batch < dateadd( MINUTE, - 60, getdate( ) ) AND login_time < dateadd( MINUTE, - 60, getdate( ) ) EXEC sp_msforeach_worker '?'
--3.以下是备用方案:
--查询死锁
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT'
--杀死死锁
KILL 155
--显示死锁相关信息
exec sp_who2 137
参考原文:https://www.cnblogs.com/guangang/articles/9242028.html