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

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

 

• 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/

 

2014-07-17 / 강성욱 / http://sqlmvp.kr

 

 

Sqlserver, 쿼리 차단, 블록킹, 잠금, 데드락, DB, mssqlserver, 락킹, block session, 블록세션, 쿼리튜닝, dB튜닝, 데이터베이스