Blocking 모니터링 (blocking spid)

내용

데이터베이스 운영을 하면서 lock을 발생시키는 프로세스와 이로 인해 대기하는 프로세스를 확인하고자 하는 경우가 생깁니다. 해당 프로세스에 해당하는 쿼리 또한 정보를 보고 싶어서 한번에 조회가 되도록 쿼리를 작성하여 사용하고 있습니다.

 

쿼리

create proc [dbo].[sp_mon_blocking]        
as                    
set nocount on                    
                    
--기초 테이블 생성              
create table #a(blocking_spid int,blocking_hostprocess int , 
	blocking_sql varchar(8000)              
   ,blockedby_spid int,blockedby_hostprocess int 
   ,blockedby_sql varchar(8000),waittime bigint,
   waittype varbinary(50),lastwaittype varchar(100))                    
              
--blocking 상황 확인 및 데이터 입력              
insert #a(blocking_spid, blocking_hostprocess,
blockedby_spid,waittime,waittype,lastwaittype)               
select spid, hostprocess, blocked,waittime,
waittype,lastwaittype from master..sysprocesses where blocked > 0                    
               
--데이터가 없으면 여기서 그만!              
if @@rowcount = 0                     
begin                    
 print 'There is no blocking spid.'                    
 return                    
end                    
                    
--필요한 변수 선언              
declare @spid int                    
declare @sql_handle binary(20)                    
declare @object_name varchar(100)         
declare @text varchar(8000)                   
              
--데이터 수집 커서              
declare CurBlocked cursor                    
for                    
select distinct blocking_spid from #a                    
union                    
select distinct blockedby_spid from #a                    
                    
open CurBlocked                    
                    
fetch next from CurBlocked into @spid                    
                    
while @@fetch_status = 0                    
begin                    
 select top 1 @sql_handle = sql_handle 
 from master..sysprocesses where spid = @spid                    
        
 select @object_name = object_name(objectid),
 @text = text from sys.dm_exec_sql_text(@sql_handle)                    
         
  
  
if @object_name is not null        
begin        
 update #a set blocking_sql = @object_name where blocking_spid = @spid                    
 update #a set blockedby_sql = @object_name, 
	blockedby_hostprocess = s.hostprocess              
 from #a a join master..sysprocesses s on a.blockedby_spid = s.spid              
 where blockedby_spid = @spid                    
end        
else        
begin        
       
 update #a set blocking_sql = @text where blocking_spid = @spid                    
 update #a set blockedby_sql = @text, blockedby_hostprocess = s.hostprocess              
 from #a a join master..sysprocesses s on a.blockedby_spid = s.spid              
where blockedby_spid = @spid                    
end        
                    
 fetch next from CurBlocked into @spid                    
end                     
                    
close CurBlocked                    
deallocate CurBlocked                    
                  
--잠그는 최 우선 주체가 되는 넘들 입력              
insert #a (blocking_spid, blocking_hostprocess, blocking_sql)                    
select distinct blockedby_spid, blockedby_hostprocess, blockedby_sql from #a                    
except                    
select distinct blocking_spid, blocking_hostprocess, blocking_sql from #a                    
                   
              
--블로킹 체인을 보여주기 위한 커서               
declare CurBlocked cursor                    
for                    
select blocking_spid from #a where blockedby_spid is null                    
                    
open CurBlocked                    
fetch next from CurBlocked into @spid                    
                    
while @@fetch_status = 0                    
begin                    
                    
WITH Blocking (blocking_spid, blocking_hostprocess, 
blocking_sql, blockedby_spid, blockedby_hostprocess, 
blockedby_sql,waittime,waittype,lastwaittype, Level)                    
AS                    
( select blocking_spid, blocking_hostprocess, 
	blocking_sql, blockedby_spid, blockedby_hostprocess, 
	blockedby_sql,waittime,waittype,lastwaittype, 
	0 AS Level  from #a  where blocking_spid = @spid                    
  union all                    
  select a.*, Level + 1  from #a a 
  join  Blocking b on a.blockedby_spid = b.blocking_spid                    
)                    
select blocking_spid, blocking_hostprocess       
,blocking_sql = substring(blocking_sql,PATINDEX('%)[A-z]%', blocking_sql)
	+1,8000-(PATINDEX('%)[A-z]%', blocking_sql)+1))        
,blockedby_spid, blockedby_hostprocess      
,blockedby_sql = substring(blockedby_sql,PATINDEX('%)[A-z]%', blockedby_sql)
	+1,8000-(PATINDEX('%)[A-z]%', blockedby_sql)+1))        
,waittime,waittype,lastwaittype, Level      
from Blocking order by Level                  
 fetch next from CurBlocked into @spid             
end                    
close CurBlocked                    
deallocate CurBlocked       
      
GO

결과