查找堵塞

--查找堵塞源
 SELECT spid 被锁进程ID, blocked 锁进程ID,status 被锁状态, login_time 登录时间, 
SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号, 
SUBSTRING(hostname,1,12) 被锁进程用户机器名称, 
SUBSTRING(DB_NAME(dbid),1,13) 被锁进程数据名称, 
cmd 被锁进程命令, 
waittype 被锁进程等待类型
 FROM master.sys.sysprocesses  WHERE SPID>50 and blocked!=0 AND spid<>blocked   order by waittime desc 


 --堵塞源的堵塞类型
 SELECT 
 CASE  WHEN waittime!=0X0000 AND open_tran>=0 AND status='runnable' THEN N'运行时间太长' 
 WHEN waittime=0X0000 AND open_tran>0 AND status='sleeping' THEN N'sleeping进程事物未提交' 
 WHEN waittime in(0x0800,0x0063) AND open_tran>=0 AND status='runnable' THEN N'没有及时取走结果集' 
 WHEN waittime=0X0000 AND open_tran>0 AND status='rollback' THEN N'超时或主动回滚' 
 WHEN waittime>=0X0000 AND open_tran>=0 AND status='runnable' THEN N'死锁以堵塞体现' 
 END  AS Reason,
 b.text SQL,DB_NAME(a.dbid) as DB,program_name,hostname,loginame
 FROM master.sys.sysprocesses a
 CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b 
 WHERE SPID=346

 

转载于:https://www.cnblogs.com/JinweiChang/p/10449923.html