블록킹 세션을 찾아 우선순위 낮은 세션 종료하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

데이터베이스 서버를 운영하다보면 다양한 쿼리 요청으로 인해 블록킹이 발생 할 수 있다. 차단 및 교착이 발생하였을 때 이를 감지하고 블록킹이 발생한 세션 중 우선순위가 가장 낮은 프로세스를 종료 할 수 있다면 원치 않는 비즈니스 중단을 예방 할 수 있다.

 

시나리오는 다음과 같다.

매일 밤 실행되는 일괄 처리 작업이 다른 프로세스를 차단하는 현상이 발생 하였을 때 일괄 처리 되는 세션을 종료시키고 싶다. 일괄 처리되는 스크립트에 라벨링을 하여 해당 세션을 강제 종료하는 방법에 대해서 알아 본다.

 

세션 식별 목적을 위해 스크립트의 시작 부분에 CONTEXT_INFO 함수를 사용하여 라벨링을 할 수 있다.

set context_info 0xdba911 -- arbitrary, and can be any value you like

 

세션 1에서 실습 테이블을 생성하고 트랜잭션을 발생시킨다. 스크립트 상단에 CONTEXT_INFO를 사용하여 세션을 식별 할 수 있도록 한다.

세션2에서는 세션1에서 사용중인 테이블에 잠금을 요청하여 블록을 발생 시킨다.

세션3에서는 현재 블록킹 된 세션을 감지하고 사용자 세션을 식별하여 종료시킬 SPID를 알려준다.

세션1

세션2

세션3

if object_id('dbo.t', 'U') is not null

drop table dbo.t;

create table dbo.t (a int, b varchar(30));

go

insert into dbo.t (a, b) values (1, 'hello')

go

set context_info 0xdba911; -- change 0xdba911 to your preferred value

begin tran

update dbo.t set b= 'world' from dbo.t where a=1

waitfor delay '00:05:00' -- wait for 5 min

commit tran;

  
 

delete from dbo.t where a=1;

 
  

set nocount on;

set deadlock_priority low;

declare @sqlcmd varchar(max);

declare @debug bit; -- 1=print out kill command, 0=execute kill command

 

set @debug = 1; -- 1=print, 0=exec

set @sqlcmd='';

 

; with cte (Session_id, Context_info) as

(

select r1.session_id, r1.context_info from sys.dm_exec_requests r1 with (nolock)

inner join sys.dm_exec_requests r2 with (nolock)

on r1.session_id = r2.blocking_session_id

where r1.session_id > 50

and r1.session_id <> @@spid

union

select s.session_id, s.context_info from sys.dm_exec_sessions s with (nolock)

inner join sys.dm_exec_requests r with (nolock)

on s.session_id = r.blocking_session_id

and r.session_id <> @@spid

)

select @sqlcmd = @sqlcmd + 'kill ' + cast(session_id as varchar) +';' + char(0x0d) + char(0x0a) from cte

where context_info = 0xdba911; -- 0xdba911 for labelling low priority sessions

if @debug = 1

print @sqlcmd;

else

exec (@sqlcmd);

 

 

세션3의 스크립트를 SQL Server Job Agent에 등록하여 주기적으로 검사하여 자동으로 블록킹된 SPID 세션을 강제 종료 할 수 있도록 설정하여 사용하면 된다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3285/detect-and-automatically-kill-low-priority-blocking-sessions-in-sql-server/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.