성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 - 다른 세션에 의한 블록킹 확인

성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석

• 다른 세션에 의한 블록킹 확인

   

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

 

SQL Server에서 요청한 작업의 결과가 느리게 반환될 때가 있다. 이는 요청 된 작업의 처리량이 많아서 처리 속도가 오래 걸릴 수도 있지만 다른 작업에 의해 대기가 발생하여 작업이 기다리게 되는 상황이 발생 할 수도 있다.

 

현재 실행을 위해 대기하는 요청을 확인 하기 위해서는 DMV 함수 sys.dm_exec_requests 를 사용할 수 있다.

Sys.dm_exec_requests 결과에서 wait_time 및 wait_type 열을 확인 하여 현재 어떤 작업이 대기 하고 있는지 또는 실행되고 있는지 확인 할 수 있다.

 

Select     session_id,     status,     command,     blocking_session_id,     wait_type,     wait_time,     last_wait_type,     wait_resource from sys.dm_exec_requests where session_id >= 50 and session_id <> @@spid;

 

 

• 세션53의 상태는 56번 세션에 의해 SELECT 작업이 대기하고 있다.
• 세션 54의 상태는 세션 55에 의해서 INSERT 작업이 대기 하고 있다.
• 세션 55의 상태는 INSERT 작업이 진행 중이며 이전대기 상태는 PAGELATCH_SH 이다.
• 세선 56의 상태는 INSERT 작업이 대기 중이며 WRITELOG (트랜잭션 커밋)로 데이터베이스 로그 플러시를 기다리고 있다.
• 세션 57의 상태는 INSERT 작업이 대기 중이며 WRITELOG로 데이터베이스 로그 플러시를 기다리고 있다.

 

세션 54의 현재 상태는 실행 중이지만 실제로는 대기 하고 있다. 하지만 대기 상태가 래치이므로 매우 짧을 것으로 예상된다.

Blocking_session_id의 정보를 보면 어떤 다른 요청에 의해서 현재 대기하고 있는지 알려준다. 세션 53의 SELECT의 경우 행을 읽으려고 KEY 잠금 리소스에 대한 56 세션의 작업을 기다리고 있다. 세션 56의 INSERT 작업이 커밋 될 때까지 세션 53의 SELECT가 되지 않는다.

 

세션 54는 세션 55의 INSERT 작업으로 해당 페이지를 수정하고 페이지의 데이터를 판독할 수 없는 불안정환 상태로 페이지 래치에 대한 세션을 기다리고 있다.

 

세션 56,57의 경우는 다른 세션으로 인한 차단은 없지만 대기 하고 있다. 이 대기 작업은 트랜잭션이 디스크 기록에 대한 커밋을 확인 하는 것으로 로그 플러시를 기다리고 있다. 디스크에 로그가 모두 기록되기 전까지 대기가 발생 한다.

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/#wait_info_current

 

2014-02-27 / 강성욱 / http://sqlmvp.kr