데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

Block 모니터링

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

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
1613 extended events 를 이용한 wait 분석 minsouk 2014.07.25 6264
1612 동일기준에대해 복수컬럼 PIVOT 사용하기 열이 2014.06.27 21319
1611 SELECT / UPDATE / INSERT 쿼리를 쉽게 만들자... [1] Hisory 2014.05.12 10870
1610 ssms 자동 언어 변경이 되면, 이렇게 설정하세요 minsouk 2014.04.27 9566
1609 [QUERY] 그룹 번호 만들기 이스트럭(강동운) 2014.04.11 8680
1608 [QUERY]연승을 구해보자! [2] 이스트럭(강동운) 2014.04.11 9462
1607 VLF 이스트럭(강동운) 2014.04.11 8076
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6400
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8616
1604 GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13560
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10632
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9379
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10526
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14751
1599 VLF 환경과 성능 jevida(강성욱) 2013.11.21 10060
» Block 모니터링 jevida(강성욱) 2013.11.21 8073
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8895
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6562
1595 DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11351
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7566





XE Login