[MSSQL] Lock Tree 보기

Lock이 발생하는 경우 Tree 형태로 해당 세션을 조회할 수 있는 쿼리이다.

SET NOCOUNT ON; 
SET CONCAT_NULL_YIELDS_NULL OFF 
GO 

WITH Blockers (SPID, Blocked, Level, Batch, WaitType, LastWaitType) 
AS 
( 
    SELECT  SPID, 
            Blocked, 
            CAST(REPLICATE('0', 4 - LEN(CAST(SPID AS VARCHAR))) + CAST(SPID AS VARCHAR) AS VARCHAR(1000)) AS Level, 
            REPLACE(REPLACE(T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS Batch, 
            R.WaitType, 
            R.LastWaitType 
    FROM    sys.SysProcesses R WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(R.SQL_Handle) T 
    WHERE   (Blocked = 0 OR Blocked = SPID) 
    AND     EXISTS (
                SELECT  SPID, Blocked, CAST(REPLICATE('0', 4 - LEN (CAST(SPID AS VARCHAR))) + CAST(SPID AS VARCHAR) AS VARCHAR(1000)) AS Level, 
                        Blocked, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS Batch, 
						R.WaitType ,R.LastWaitType 
                FROM    sys.sysprocesses R2 WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(R.SQL_Handle) T 
                WHERE   R2.Blocked = R.SPID 
                AND     R2.Blocked <> R2.SPID ) 
    UNION ALL 
    SELECT  R.SPID, 
            R.Blocked, 
            CAST(Blockers.LEVEL + RIGHT(CAST((1000 + R.SPID) AS VARCHAR(100)), 4) AS VARCHAR(1000)) AS Level, 
            REPLACE(REPLACE(T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS BATCH, 
            R.WaitType, 
            R.LastWaitType 
    FROM    sys.sysprocesses AS R WITH (NOLOCK) CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 
    INNER JOIN Blockers ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID 
)
SELECT  REPLICATE('│   ', LEN(Level) / 4 - 2) + CASE WHEN (LEN(Level)/4 - 1) = 0 THEN 'HEAD ─ ' ELSE '├─ ' END + CAST(SPID AS VARCHAR (10)) AS "Blocking Tree", 
		Batch AS "SQL Statement",
		CAST(RTRIM(LTRIM(SUBSTRING(CAST(ses.CONTEXT_INFO AS VARCHAR(128)), CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), 2), CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), 2) + 1) - CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), 2))))) AS BIGINT) AS AXSession,
		RTRIM(LTRIM(SUBSTRING(CAST(ses.CONTEXT_INFO AS VARCHAR(128)),1,CHARINDEX(' ', CAST(ses.CONTEXT_INFO AS VARCHAR(128)), 2)))) AS AXUser,
        WaitType,
        LastWaitType,
		ses.Host_Name,
		ses.Login_Name,
		ses.Program_Name,
        GETDATE() AS Time 
FROM    Blockers b WITH (NOLOCK)
LEFT JOIN sys.dm_exec_sessions ses ON b.spid = ses.session_id
ORDER BY Level ASC;