MS SQL Server 排查阻塞和查找被锁语句

-- 方法1
SELECT
'资源类型' = t1.resource_type,
'来源数据库' = CONVERT(CHAR(25), DB_NAME(resource_database_id)),
'数据库中与资源相关联的实体的 ID' = t1.resource_associated_entity_id,
'锁模式' = t1.request_mode,  --锁的模式:S-共享锁,U-更新锁,X-排他锁,IS/IU/IX-意向共享/更新/排他锁
'被堵塞的语句' = t1.request_session_id,  
'造成左边语句堵塞的语句' = t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
--KILL blocking_session_id

--方法2
--1.查找blocked大于0的被锁进程, 观察并找到可疑的spid
SELECT '进程ID' = spid
, 'Windows线程ID' = kpid
, '当前状态' = CONVERT(CHAR(25), status)
, '正在阻塞请求的会话的ID' = blocked
, '当前等待时间(ms)' = waittime
, '进程的累计CPU时间' = cpu
, '进程的累计磁盘IO次数' = physical_io
, '当前正由进程使用的数据库' = CONVERT(CHAR(25), DB_NAME(dbid))
, '正在执行的命令' = CONVERT(CHAR(16), cmd)
, '工作站名称' = CONVERT(CHAR(25), hostname)
, '执行命令的用户' = CONVERT(CHAR(15), SUSER_NAME(uid))
FROM sys.sysprocesses WHERE blocked >0 ORDER BY waittime desc;
--2.依据spid查询分析此进程阻塞的源头
SELECT * FROM sys.sysprocesses WHERE spid =1204
--3.查看此spid进程执行的SQL语句
DBCC inputbuffer(1204);
--4.直接干掉死锁进程
KILL 259;