1 CREATE procedure sp_who_lock  2 as  3 begin  4 declare @spid int  5 declare @blk int  6 declare @count int  7 declare @index int  8 declare @lock tinyint  9 set @lock=0 10 create table #temp_who_lock 11 ( 12 id int identity(1,1), 13 spid int, 14 blk int 15 ) 16 if @@error<>0 return @@error 17 insert into #temp_who_lock(spid,blk) 18 select 0 ,blocked 19 from (select * from master..sysprocesses where blocked>0)a 20 where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0) 21 union select spid,blocked from master..sysprocesses where blocked>0 22 if @@error<>0 return @@error 23 select @count=count(*),@index=1 from #temp_who_lock 24 if @@error<>0 return @@error 25 if @count=0 26 begin 27 select ‘没有阻塞和死锁信息‘ 28 return 0 29 end 30 while @index<=@count 31 begin 32 if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid)) 33 begin 34 set @lock=1 35 select @spid=spid,@blk=blk from #temp_who_lock where id=@index 36 select ‘引起数据库死锁的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘进程号,其执行的SQL语法如下‘ 37 select @spid, @blk 38 dbcc inputbuffer(@spid) 39 dbcc inputbuffer(@blk) 40 end 41 set @index=@index+1 42 end 43 if @lock=0 44 begin 45 set @index=1 46 while @index<=@count 47 begin 48 select @spid=spid,@blk=blk from #temp_who_lock where id=@index 49 if @spid=0 50 select ‘引起阻塞的是:‘+cast(@blk as varchar(10))+ ‘进程号,其执行的SQL语法如下‘ 51 else 52 select ‘进程号SPID：‘+ CAST(@spid AS VARCHAR(10))+ ‘被‘ + ‘进程号SPID：‘+ CAST(@blk AS VARCHAR(10)) +‘阻塞,其当前进程执行的SQL语法如下‘ 53 dbcc inputbuffer(@spid) 54 dbcc inputbuffer(@blk) 55 set @index=@index+1 56 end 57 end 58 drop table #temp_who_lock 59 return 0 60 end

 1 SELECT  L1.resource_type , 2         DB_NAME(L1.resource_database_id) AS DatabaseName , 3         CASE L1.resource_type 4           WHEN ‘OBJECT‘ 5           THEN OBJECT_NAME(L1.resource_associated_entity_id, 6                            L1.resource_database_id) 7           WHEN ‘DATABASE‘ THEN ‘DATABASE‘ 8           ELSE CASE WHEN L1.resource_database_id = DB_ID() 9                     THEN ( SELECT   OBJECT_NAME(object_id,10                                                 L1.resource_database_id)11                            FROM     sys.partitions12                            WHERE    hobt_id = L1.resource_associated_entity_id13                          )14                     ELSE NULL15                END16         END AS ObjectName ,17         L1.resource_description ,18         L1.request_session_id ,19         L1.request_mode ,20         L1.request_status21 FROM    sys.dm_tran_locks AS L122         JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id23 WHERE   L1.request_status <> L2.request_status24         AND ( L1.resource_description = L2.resource_description25               OR ( L1.resource_description IS NULL26                    AND L2.resource_description IS NULL27                  )28             )29 ORDER BY L1.resource_database_id ,30         L1.resource_associated_entity_id ,31         L1.request_status ASC;

1 SELECT  W.session_id AS waiting_session_id , 2         W.waiting_task_address , 3         W.wait_duration_ms , 4         W.wait_type , 5         W.blocking_session_id , 6         W.resource_description 7 FROM    sys.dm_os_waiting_tasks AS W 8 WHERE   W.wait_duration_ms > 5000 9         AND blocking_session_id IS NOT NULL;

 1 SELECT  request_session_id ,  2         resource_type ,  3         DB_NAME(resource_database_id) AS DatabaseName ,  4         OBJECT_NAME(resource_associated_entity_id) AS TableName ,  5         request_mode ,  6         request_type ,  7         request_status  8 FROM    sys.dm_tran_locks AS L  9         JOIN sys.all_objects AS A ON L.resource_associated_entity_id = A.object_id 10 WHERE   request_type = ‘LOCK‘ 11         AND request_status = ‘GRANT‘ 12         AND request_mode IN ( ‘X‘, ‘S‘ ) 13         AND A.type = ‘U‘ 14         AND resource_type = ‘OBJECT‘ 15         AND L.resource_database_id = DB_ID();