SQL Server에서 차단을 확인하는 다양한 방법

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server는 많은 수의 동시 사용자 요청을 처리한다. 수 많은 클라이언트가 요청을 하면 여러 프로세스가 동일한 자원에 대해 동시에 액세스하기 때문에 충돌이 발생 할 수 있다. 이때 차단이 발생하는데 SQL Server에서 발생하는 차단에 대해서 확인 하는 여러 가지 방법에 대해서 알아 본다.

 

[SSMS 작업 모니터]

SSMS에서 제공하는 작업 모니터는 SQL Server의 리소스 및 프로세스에 대한 잠금 정보를 확인 할 수 있다. SSMS의 개체 탐색기에서 [서버] –[마우스 오른쪽 클릭] –[작업모니터]를 선택한다.

 

 

프로세스 탭을 클릭하면 페이지가 확장되고 현재 실행되고 있는 프로세스에 대한 정보와 차단되는 프로세스ID를 확인 할 수 있다.

 

 

차단 프로세스를 종료 하고 싶은 경우에는 해당 목록에서 마우스 오른쪽 버튼을 클릭하여 프로스스 중지를 선택 한다.

 

 

[성능모니터]

SQLServer:Locks 항목에서 발생하는 잠금에 대해서 확인 할 수 있다.

 

  • Average Wait Time(ms) : 대기한 각 잠금 요청에 대한 평균 대기 시간(밀리초)
  • Lock Requests/sec : 잠금 관리자에서 요청한 새 잠금 및 잠금 반환 수
  • Lock Timeouts (timeout >0)/sec : 시간 초과된 잠금 요청 수. NOWAIT 잠금에 대한 요청이 포함되어 있지 않다.
  • Lock Timeouts/sec : 시간 초과된 잠금 요청 수
  • Lock Wait Time (ms) : 마지막 1초 동안의 잠금에 대한 총 대기 시간(밀리초)
  • Lock Waits/sec : 즉시 처리될 수 없어서 잠금 허가 전에 호출자가 대기해야 하는 잠금 요청 수
  • Number of Deadlocks/sec : 교착 상태를 일으킨 잠금 요청 수

 

 

[DMV]

Sys.dm_exec_requests 에서는 현재 요청에 대한 쿼리 계획, 요청이 실행된 시간, 블록킹 세션, 요청을 기다리는 자원의 이름 등 자세한 정보가 포함되어 있다.

USE [master]

GO

SELECT session_id

,blocking_session_id

,wait_time

,wait_type

,last_wait_type

,wait_resource

,transaction_isolation_level

,lock_timeout

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0

GO

 

 

  • Blocking_session_id : 블록킹 세션의 SPID
  • Wait_type : 대기의 종류
  • Wait_time : 대기 시간(밀리초)
  • Last_wait_type : 마지막 대기 타입
  • Wait_resource : 요청을 기다리는 자원
  • Transaction_isolation_level : 트랜잭션 격리 수준
  • Lock_timeout : 잠금 제한 시간

 

 

Sys.dm_tran_locks에서는 현재 잠금에 대한 차단 프로세스를 확인 할 수 있다.

USE [master]

GO

SELECT * from sys.dm_tran_locks

 

 

 

다음 스크립트는 sys.dm_tran_locks 와 sys.partitions를 사용하여 특정 데이터베이스의 잠금을 확인한다.

USE [master]

GO

SELECT tl.resource_type

,tl.resource_associated_entity_id

,OBJECT_NAME(p.object_id) AS object_name

,tl.request_status

,tl.request_mode

,tl.request_session_id

,tl.resource_description

FROM sys.dm_tran_locks tl

LEFT JOIN sys.partitions p

ON p.hobt_id = tl.resource_associated_entity_id

WHERE tl.resource_database_id = DB_ID()

GO

 

 

 

Sys.dm_os_waiting_taasks는 차단된 프로세스의 정보를 나타낸다. 차단된 프로세스는 session_id에서 확인 할 수 있다.

USE [master]

GO

SELECT w.session_id

,w.wait_duration_ms

,w.wait_type

,w.blocking_session_id

,w.resource_description

,s.program_name

,t.text

,t.dbid

,s.cpu_time

,s.memory_usage

FROM sys.dm_os_waiting_tasks w

INNER JOIN sys.dm_exec_sessions s

ON w.session_id = s.session_id

INNER JOIN sys.dm_exec_requests r

ON s.session_id = r.session_id

OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

WHERE s.is_user_process = 1

GO

 

 

 

[SQL Profiler]

프로파일러에서 잠금 및 교착에 관한 이벤트를 추적할 수 있다. 프로파일러를 실행하여 모든 이벤트 표시를 선택한다. Event에서 Locks 항목을 체크한다.

 

 

잠금 및 교착이 발생 하였을 때 현재 상태를 확인하고 해결하기 위해 도구 사용법을 습득하고 잠금에 관한 정보를 추적하여 근본적인 원인을 해결 할 수 있도록 한다.

 

 


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





profile

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

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.