因为所处的工作原因,经常会处理SQL运行慢的情况,需要抓出那条捣乱的虫子以表示自己的清白,所以很无奈的看了网上N多的资料,然后try了N+N遍,加上客户的试用,哈,一个比较完整的抓阻塞的脚本就出炉了。
0001 SET NOCOUNT ON 0002 declare @sid integer, 0003 @objid integer, 0004 @old_sid integer, 0005 @dbid integer, 0006 @db_name varchar(255) 0007 begin 0008 -- 纪录时间点 0009 select '====> 时间:' , 0010 10000 * datepart(yy, getdate()) + 100 * datepart(mm, getdate()) + datepart(dd, getdate()), 0011 10000 * datepart(hh, getdate()) + 100 * datepart(mi, getdate()) + datepart(ss, getdate()) 0012 0013 -- 简单列举当前线程的锁的情况: blocked>0 就是索住了 0014 select '====> 进程情况:=====================================' 0015 select 0016 spid as 进程号, 0017 case when blocked >0 then '被'+convert(varchar,blocked)+'进程堵塞' 0018 else '无堵塞' end as 'info',* 0019 from sysprocesses 0020 order by blocked,spid 0021 0022 -- 为了随后的游标操作不影响sql本身,所以先保存锁信息到临时表,再作分析 0023 select * into #temp from master..syslockinfo order by req_spid 0024 select '====>后台锁情况简单列表=====================================' 0025 select * from #temp order by req_spid 0026 0027 -- 对每个进程做具体的锁分析,列举设计的表和正在处理的脚本 0028 select req_spid,rsc_dbid,rsc_objid into #temp1 from #temp group by req_spid,rsc_dbid,rsc_objid 0029 select @old_sid = 0 0030 declare sid_cur CURSOR for 0031 select req_spid,rsc_dbid,rsc_objid from #temp1 order by req_spid,rsc_objid 0032 open sid_cur 0033 if @@error != 0 0034 begin 0035 close sid_cur 0036 deallocate sid_cur 0037 select error_no = 1,error_info = '打开游标错误[1]' 0038 drop table #temp 0039 drop table #temp1 0040 end 0041 else 0042 begin 0043 fetch sid_cur into @sid, @dbid, @objid 0044 while @@fetch_status = 0 0045 begin 0046 if @old_sid <> @sid 0047 begin 0048 select '====>进程'+convert(varchar,@sid)+'锁情况 ============' 0049 exec sp_lock @sid 0050 select '====>进程'+convert(varchar,@sid)+'涉及的脚本或者过程:============= ' 0051 dbcc inputbuffer(@sid) 0052 end 0053 select @db_name = 'select ''====>进程'+convert(varchar,@sid)+' 涉及表 ''+ name from '+ 0054 db_name(@dbid) +'..sysobjects where type =''U'' ' 0055 +' and id = ' + convert(varchar,@objid) 0056 exec ( @db_name ) 0057 select @old_sid = @sid 0058 fetch sid_cur into @sid, @dbid, @objid 0059 end 0060 drop table #temp 0061 drop table #temp1 0062 close sid_cur 0063 deallocate sid_cur 0064 end 0065 end 0066 SET NOCOUNT off 0067
这个脚本如果出现运行时间过长,会导致不准确。原因很简单嘛,后面的dbcc inputbuffer(@sid)读的是实时更新的数据,如果太慢就不对了,读到的可能是这个进程新执行的一个过程。这个脚本在实践中使用过多次,效果比较明显,基本上每次都能抓出那个捣乱的存储过程或者sql语句,对于快速的定位和处理问题起到了很大的作用。
今天太晚,有空来分析一下出来的结果,如何才能看出死锁了或者某个过程过慢。。。。。。。