2013.05.04 DBNuni Study 에서 발표하였던 Blocking session를 볼 수 있는 스크립트 입니다. 가장 기본적인 형태이며, 필요하신 부분은 더 추가하시면 될 듯 합니다. 일단 임의적으로 블럭킹을 발생시키기 위해서 아래와 같은 스크립트를 실행 시킵니다.
/******************************************************************************
* 블럭킹TEST를 하기 위하여 임시 테이블을 만든다.
******************************************************************************/
USE DBA
GO
CREATE TABLE DBA.DBO.TB_BLOCKING (SEQ INT IDENTITY(1, 1), CNT INT);
INSERT INTO DBA.DBO.TB_BLOCKING VALUES (1), (2), (3);
/******************************************************************************
* 세션 1 에서 아래와 같이 트랜잭션 커밋을 하지 않는다.
******************************************************************************/
BEGIN TRAN
UPDATE [DBA].[dbo].[TB_BLOCKING] SET CNT = 10 WHERE SEQ = 1
-- COMMIT
/******************************************************************************
* 세션 2
* 새 창을 열어서 아래와 같이 SELECT를 한다.
******************************************************************************/
SELECT * FROM [DBA].[dbo].[TB_BLOCKING] WITH(TABLOCK
이렇게 실행을 하게 되면 아래 스크린샷 처럼 실행 중 상태가 되며, 블럭킹이 걸렸다는 의미 입니다.
이와 같은 상태에서 아래 스크립트를 새로운 세션에서 실행하게 되면 블럭킹 정보가 출력이 됩니다.
* 세션 3
******************************************************************************/
SELECT '블럭카운트 : ' + CAST(CNT AS VARCHAR(10)) FROM ( SELECT COUNT(*) CNT FROM SYSPROCESSES WHERE BLOCKED <> 0 ) AS A;
WITH BLOCKING
AS (
SELECT
SPID,
BLOCKED,
CAST(SPID AS VARCHAR(100)) AS BLOCKTREE,
Level = 0 ,
CAST(SPID AS VARBINARY(4000)) AS BLOCK_DEPTH
FROM MASTER.DBO.SYSPROCESSES
WHERE BLOCKED = 0
AND SPID > 50
AND SPID IN (SELECT BLOCKED
FROM MASTER.DBO.SYSPROCESSES WHERE BLOCKED <> 0)
UNION ALL
SELECT DS.SPID ,
DS.BLOCKED ,
CAST(BC.BLOCKTREE + ' > '
+ CAST(DS.SPID AS VARCHAR(100)) AS VARCHAR(100)) ,
Level + 1 ,
CAST(BLOCK_DEPTH + CAST(DS.BLOCKED AS BINARY(4)) AS VARBINARY(4000))
FROM BLOCKING AS BC
INNER JOIN MASTER.DBO.SYSPROCESSES AS DS
ON DS.BLOCKED = BC.SPID
)
SELECT
BC.BLOCKTREE AS [블럭TREE],
CONVERT(VARCHAR, DATEADD(S ,SP.waittime / 1000, ''), 8 ) AS [HH:MM:SS],
DB_NAME(SP.dbid) AS [실행DB],
SP.lastwaittype AS [대기유형],
SP.waitresource AS [대기자원],
SP.cmd AS [차단중인명령어],
SP.hostname AS [호스트명],
SP.program_name AS [실행프로그램],
SP.loginame AS [실행계정],
SQL_TEXT.TEXT AS [실행문]
--SP.*
FROM BLOCKING AS BC
INNER JOIN MASTER.DBO.SYSPROCESSES AS SP
ON BC.SPID = SP.SPID
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
ORDER BY BLOCK_DEPTH
보시는 봐와 같이 54번 세션이 56번 세션을 가로막고 있다고 출력이 되고 있습니다.