[MS-SQL] MS-SQL 계층형 쿼리 작성 (LOCK SESSION 조회)

oracle에서는 계층형 쿼리를 사용할 때 start with.. connect by와 같은 내장함수로 간단하계 트리구조를 조회할 수 있다.
하지만 MS-SQL은 with절을 통해 재귀로 구현하는 방법이 있다.

구현하는 방식은 아래와 같다.

WITH <WITH_테이블>(column1, column2...) AS 
(
	-- 루트노드
	SELECT *, 0 AS LEVEL
	FROM A		
	
	UNILN ALL
	
	-- 자식노드
	SELECT *, <WITH_테이블>.LEVEL + 1 
	FROM A JOIN <WITH_테이블> ON ...	
)

이 포멧을 토대로 LOCK SESSION 을 조회하는 쿼리를 작성해보자.

 

with blocking as (
	
	select session_id, blocking_session_id, 0 as level
	from sys.dm_exec_requests
	where blocking_session_id = 0

	union all

	select blocked.session_id, blocked.blocking_session_id, blocking.level+1 as level
	from sys.dm_exec_requests blocked 
	inner join blocking on blocking.session_id = blocked.blocking_session_id
	where blocked.blocking_session_id <> 0
)
select a.*
from blocking a;

 

아주 간단하게 위 처럼 작성 할 수 있다.

 

이걸 응용해서 복잡한 blocking, blocked session 조회하는 쿼리를 계층형으로 작성한다면

with block_session as (
	-- blocked session (lock을 잡고있는 sessioin)
	select 
		block.session_id,
		block.blocking_session_id,
		block.start_time, 
		block.status, 
		block.command, 
		(select text from sys.dm_exec_sql_text(block.sql_handle)) as sql_text, 
		block.wait_type,
		sess.login_name,
		sess.host_name,
		sess.program_name,
		0 as level
	from sys.dm_exec_requests block, sys.dm_exec_sessions sess
	where block.session_id = sess.session_id
	and blocking_session_id = 0

	union all

	-- waiting session (lock이 풀리길 기다리는 session)
	select 
		block.session_id,
		block.blocking_session_id,
		block.start_time, 
		block.status, 
		block.command, 
		(select text from sys.dm_exec_sql_text(block.sql_handle)) as sql_text, 
		block.wait_type,
		sess.login_name,
		sess.host_name,
		sess.program_name,
		block_session.level + 1
	from sys.dm_exec_requests block, sys.dm_exec_sessions sess, block_session
	where block.session_id = sess.session_id
	and block.blocking_session_id = block_session.session_id
	and block.blocking_session_id <> 0
)
select 
	replicate('--', level+1) + host_name as sess_id,
	*
from block_session;

 

과 같이 결과를 얻을 수 있다.