블로킹 세션 조회하기

개발자 한분이 문의를 했습니다. 나도 개발자였는데... ㅠㅠ
클러스터드 인덱스가 잡혀 있는데 delete 하려니까 시간이 너무 걸려요.
아마도 누군가가 트랜젝션을 걸고 테이블을 수정한 후 트랜젝션을 종료하지 않아서
또는 잠금이 오래 지속되어서 대기 상태일 것같았습니다.
그러면 어떤 세션이 어떤 세션에 의해 블로킹을 당했는지 알려면 어떻게 해야 할까요?
sys.sysprocesses테이블을 이용한다면 blocked열을
sys.dm_exec_requests뷰를 이용한다면 blocking_session_id열을 참조하면 됩니다.
근데 이 사건이 굉장이 복잡하다면??
예를 들어서 1번세션때문에 2번이 블로킹, 2번 세션 때문에 3번세션이 블로킹, ... 99번 세션때문에...
줄줄이 추적해 나가기가 힘들겠죠.
그래서 CTE를 이용해 블로킹 트리를 한번에 조회하는 쿼리를 작성해보았습니다.
;WITH BlockProcesses (session_id, block_tree) AS (
SELECT A.blocking_session_id, block_tree = CONVERT(VARCHAR(1000), A.blocking_session_id)
  FROM sys.dm_exec_requests A
       LEFT OUTER JOIN sys.dm_exec_requests B ON A.blocking_session_id = B.session_id
 WHERE A.blocking_session_id > 0 AND B.session_id IS NULL
UNION ALL
SELECT A.session_id, block_tree = CONVERT(VARCHAR(1000), B.block_tree + '>' + CONVERT(VARCHAR(10), A.session_id))
  FROM sys.dm_exec_requests A
       INNER JOIN BlockProcesses B ON A.blocking_session_id = B.session_id)

SELECT DISTINCT
       A.session_id
       , Z.block_tree
       , text = ISNULL(ISNULL(CONVERT(VARCHAR(MAX), object_name(D.objectid, D.dbid)), D.text),'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), A.session_id) + ')')
       , D.objectid
       , dbname = db_name(D.dbid)
       , D.dbid
       , A.status
       , A.cpu_time
       , A.memory_usage
       , A.total_scheduled_time
       , A.total_elapsed_time
       , A.last_request_start_time
       , A.last_request_end_time
       , worker_time = DATEDIFF(SECOND, A.last_request_start_time, CASE WHEN A.status = 'running' THEN GETDATE() ELSE A.last_request_end_time END)
       , completion_time = C.estimated_completion_time / 1000.0
       , C.percent_complete
       , A.reads
       , A.writes
       , A.logical_reads
       , transaction_isolation_level = CASE A.transaction_isolation_level WHEN 0 THEN '0'
                                                                        WHEN 1 THEN '1 - READ UNCOMMITTED'
                                                                        WHEN 2 THEN '2 - READ COMMITTED'
                                                                        WHEN 3 THEN '3 - REPEATABLE READ'
                                                                        WHEN 4 THEN '4 - SERIALIZABLE'
                                                                        WHEN 5 THEN '5 - SNAPSHOT' END
       , A.lock_timeout
       , A.deadlock_priority
       , A.row_count
       , A.prev_error
       , B.num_reads
       , B.num_writes
       , B.last_read
       , B.last_write
       , B.net_packet_size
       , C.command
       , C.blocking_session_id
       , C.wait_type
       , C.wait_time
       , C.last_wait_type
       , C.wait_resource
       , C.granted_query_memory
       , A.host_name
       , A.program_name
       , A.client_interface_name
       , A.login_name
       , B.client_net_address
  FROM sys.dm_exec_sessions A
       LEFT OUTER JOIN sys.dm_exec_connections B ON A.session_id = B.session_id
       LEFT OUTER JOIN sys.dm_exec_requests C ON A.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(B.most_recent_sql_handle) D
       INNER JOIN BlockProcesses Z ON A.session_id = Z.session_id
 ORDER BY Z.block_tree
테스트를 위해 다음과 같은 상황을 가정해보았습니다.
--* 첫번째 세션:
BEGIN TRAN
DROP TABLE TBL1

--* 두번째 세션:
BEGIN TRAN
SELECT * FROM TBL1

--* 세번째 세션:
BEGIN TRAN
SELECT * FROM TBL1
첫번째 세션에서 시작한 트랜잭션이 아직 끝나지 않았기 때문에 두번째 세션에서의 SELECT는 블로킹 당할 것입니다.
세번째 세션도 두번째 세션때문에 블로킹 당할것입니다.
쿼리의 실행 결과는 다음과 같습니다.

1509세션이 원흉임을 쉽게 알 수 있습니다.