[MSSQL] blocking process 조회 쿼리

2023. 6. 1. 16:26· [DB] SQL SERVER
반응형

데이터베이스 운영 과정에서 데이터베이스의 부하를 발생시키는 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)

반응형
저작자표시 비영리 동일조건 (새창열림)

'[DB] SQL SERVER' 카테고리의 다른 글

[MSSQL] Lock transaction monitoring (DBA)  (1) 2023.06.07
[MSSQL] SET NOCOUNT ON  (0) 2023.06.07
[MSSQL] Backup, DBCC 등 진행율 확인하기  (0) 2023.06.01
[MSSQL] Cluster log 확인하기 (PowerShell)  (2) 2023.06.01
[MSSQL] disk usage (데이터베이스 디스크 사용량 조회)  (1) 2023.05.24
'[DB] SQL SERVER' 카테고리의 다른 글
  • [MSSQL] Lock transaction monitoring (DBA)
  • [MSSQL] SET NOCOUNT ON
  • [MSSQL] Backup, DBCC 등 진행율 확인하기
  • [MSSQL] Cluster log 확인하기 (PowerShell)
mewoni
mewoni
Mewoni's DB Worldmewoni 님의 블로그입니다.
mewoni
Mewoni's DB World
mewoni
  • 전체 글 보기
    • DBA
    • [DB] ORACLE
    • [DB] POSTGRESQL
    • [DB] SQL SERVER
    • [DB] SAP HANA DB
    • [SERVER]
    • [SAP]
    • [E] TOOLS
    • [E] MONITORING
    • [E] IT 이모저모
    • [STUDY] DAP
    • [STUDY] SQLP
    • [STUDY] QUIZ
    • [STUDY] 대용량데이터베이스솔루션
    • [STUDY] DB모델링 설계 및 구축 실무과정

블로그 메뉴

  • [LINK] Coding Factory
  • [LINK] My Jamong
  • [LINK] iheedol
  • [LINK] saponmimi
  • [LINK] Data Science Lab
  • [비공개] 글쓰기
  • [비공개] 관리

인기 글

전체
오늘
어제
hELLO · Designed By 정상우.v4.2.2
mewoni
[MSSQL] blocking process 조회 쿼리
상단으로

티스토리툴바

개인정보

  • 티스토리 홈
  • 포럼
  • 로그인

단축키

내 블로그

내 블로그 - 관리자 홈 전환
Q
Q
새 글 쓰기
W
W

블로그 게시글

글 수정 (권한 있는 경우)
E
E
댓글 영역으로 이동
C
C

모든 영역

이 페이지의 URL 복사
S
S
맨 위로 이동
T
T
티스토리 홈 이동
H
H
단축키 안내
Shift + /
⇧ + /

* 단축키는 한글/영문 대소문자로 이용 가능하며, 티스토리 기본 도메인에서만 동작합니다.