Block 모니터링.

Block 모니터링.

 

데드락(DeadLock)에 관해서는 지난 아티클에서 알아 보았습니다.

데드락 관련 링크 : http://blog.naver.com/jevida/140164120810

 

이번에는 블로킹 모니터링을 하도록 하겠습니다.

 

[SP_Lock]이라는 시스템 저장프로시저는 잠금과 관련된 정보를 제공합니다. 하지만 블로킹이 발생 하였을 때 블로킹 하는 프로세스와 블로킹 당하는 프로세스의 관계를 파악하기에는 불편함이 있습니다.

 

Sysprocess 와 inpubuffer를 통하여 블로킹의 관계와 최근 수행된 이벤트 정보를 확인해 보도록 하겠습니다.

 

BEGIN TRY     DROP TABLE #TEMP END TRY BEGIN CATCH END CATCH GO   BEGIN TRY     DROP TABLE #TEMP2 END TRY BEGIN CATCH END CATCH GO   SELECT     IDENTITY(INT, 1,1) AS NUM ,SPID, BLOCKED, WAITTIME, WAITTYPE, LASTWAITTYPE, STATUS, LOGINAME, HOSTNAME, LAST_BATCH, DB_NAME(DBID) AS [DB] INTO #TEMP FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0 OR (SPID IN (SELECT BLOCKED FROM SYS.SYSPROCESSES))   CREATE TABLE #TEMP2(NUM INT IDENTITY, SPID INT, EVENTTYPE NVARCHAR(500), PARAMETERS INT, EVENTINFO NVARCHAR(500))   DECLARE @MIN INT = 1 DECLARE @MAX INT DECLARE @QUERY NVARCHAR(500) DECLARE @SPID INT SET @MAX = (SELECT MAX(NUM) FROM #TEMP)   WHILE @MIN <= @MAX BEGIN      SELECT @QUERY = 'DBCC INPUTBUFFER(' + CONVERT(NVARCHAR(10), SPID) + ')', @SPID = SPID FROM #TEMP WHERE NUM = @MIN          INSERT #TEMP2 (EVENTTYPE, PARAMETERS, EVENTINFO)     EXEC (@QUERY)          UPDATE #TEMP2 SET SPID = @SPID WHERE NUM = @@IDENTITY          SET @MIN = @MIN + 1 END   SELECT * FROM #TEMP SELECT * FROM #TEMP2

 

첫번째 세션에 BEGIN TRAN을 이용하여 잠금을 생성합니다.

두번째 세션에서 현재 잠금이 걸려있는 테이블을 조회 합니다.

두번째 세션에서 쿼리가 대기 상태로 인하여 기다리는 것을 확인 할 수 있습니다.

 

다른 세션에서 위의 쿼리를 실행하면 블로킹의 상관관계와 어떤 쿼리문이 실행되었는지 확인 할 수 있습니다.

 

 

SPID 56번이 블로킹 당한 것은 SPID 55번 때문이라는 것을 확인 할 수 있습니다.

DBCC INPUTBUFFER을 통하여 각 SPID의 최근 실행 명령어가 나타남을 확인 할 수 있습니다.

 

[참고 자료]

SQL Server 2000/2005 튜닝 – 정원혁, 손광수-

 

http://SQLMVP.KR