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
