[MSSQL] blocking process 조회 쿼리

데이터베이스 운영 과정에서 데이터베이스의 부하를 발생시키는 LOCK 상태 프로세스에 대한 정보를 보기 쉽게 조회할 수 있는 저장 프로시저 작성

 

-- 프로시저 실행
sp_mon_blocking

 

/************ 프로시저 생성 스크립트 ************/
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

 

 

 

(출처 : https://iheedol.tistory.com/entry/Blocking-%EB%AA%A8%EB%8B%88%ED%84%B0%EB%A7%81-blocking-spid)