블로그 이미지

현재 Blocking Session 보기

2013.05.06 20:57 Posted by 건방진연이

2013/05/05 - [SQLNULL] Study - 나만의 SQL Server 2008 성능 대시보드 만들기 에서 발표하였던 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번 세션을 가로막고 있다고 출력이 되고 있습니다.