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

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 항목을 체크한다.

 

 

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

 

 

2014-05-12 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 잠금, 교착, lock, wait, sys.dm_exec_requests, sys.dm_tran_locks, sys.dm_os_waiting_tasks