Block 모니터링

jevida(강성욱) 2013.11.21 08:31 Views : 8084

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 튜닝


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp




No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 496
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6418
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8636
1604 GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13575
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10643
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9391
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10538
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14764
1599 VLF 환경과 성능 jevida(강성욱) 2013.11.21 10084
» Block 모니터링 jevida(강성욱) 2013.11.21 8084
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8906
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6574
1595 DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11390
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7574
1593 SSAS Backup 자동화 하기 jevida(강성욱) 2013.11.18 8182
1592 SSAS - MDX 스크립트 jevida(강성욱) 2013.11.18 7273
1591 SSAS - MDX 쿼리 jevida(강성욱) 2013.11.18 8153
1590 SSAS - MDX 활용 jevida(강성욱) 2013.11.12 7193
1589 SSAS - 집계 최적화 jevida(강성욱) 2013.11.12 7414
1588 SSAS - 사용자 계층 설계 jevida(강성욱) 2013.11.12 6615
1587 SSAS - 집계 설계 이해 - (3)집계 마법사에 특성 추가 jevida(강성욱) 2013.11.06 6975





XE Login