[MSSQL] Lock transaction monitoring (DBA)

 

Transact-SQL / SP 등의 실행으로 데이터베이스가 lock 상태에 있을 때, 

sysprocesses 테이블을 통해 해당 잠금 상태에 대한 정보를 조회 (SID, 대기시간, 상태 등)

 

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_mon_blocking]    Script Date: 2023-06-07 오후 12:55:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





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