[MSSQL] 성능모니터 지표 Lock Waits/sec, Latch Waits/sec 가 높아질 때 추적하는 방법

Lock Waits/sec, Latch Waits/sec 수치가 높은 현상

---

Batch Requests 수치가 증가하면서 Lock Waits 수치가 200 이상으로 증가하였습니다.

Lock Waits 수치가 증가하면서 Lock Timeouts 수치도 함께 증가하였고

Average Wait Time 수치가 2000ms (2초) 이상으로 증가하였습니다.

지표	의미
Batch Requests/sec	DB 에 호출하는 초당 쿼리 수  ( 하나의 프로시저에서 여러개의 쿼리를 호출하더라도 1의 수치만 증가 )
Lock Waits/sec	초당 잠금을 획득하기 위해 대기하는 수
Lock Timeouts/sec	초당 잠금을 획득하기 위해 대기하다가 Timeout 되는 수
Average Wait Time (ms)	쿼리가 완료되기 까지 지연된 시간

왜 이런 현상이 나왔을까?

---

가정해 봅니다.

먼저 락의 이슈가 있는 쿼리의 호출이 증가하였습니다.

디스크 읽기/쓰기 응답속도이라던지, 큐의 밀림 등으로 인해 호출이 바로바로 완료되지 못하고 쌓이고 있습니다.

혹은 앞에서 발생한 트랜잭션이 계속 활성화 되어 있어,

뒤 세션에서 호출한 트랜잭션은 대기해야 하는 상황이 될 수 있습니다.

근거 확보를 위한 추적

---

1. 트랜잭션이 정말 지연되고 있는지?

어떤 세션이 락을 잡고 있는지,

어떤 세션이 블락킹을 당하고 있는지 얼마나 오랫동안 블락킹 되는지

어떤 쿼리로 블락킹을 당했는지에 대해 조사를 해봅니다.

select
  dtl.request_session_id,
  DB_NAME(resource_database_id) AS database_name,
  resource_type,                                          -- 잠금 리소스 유형 DATABASE, PAGE, KEY, RID 등이 될 수 있음
  request_mode,                                           -- 잠금 종류 S, X, IX 등이 될 수 있음
  request_status,                                         -- 잠금 상태 GRANTED, WAIT 등이 될 수 있음
  der.blocking_session_id,                                -- 블락하고 있는 세션에 대한 정보 확인
  DATEDIFF(ss,der.start_time,GETDATE()) AS Running_Time,  -- 수행시간
  dest.text,                                              -- 이슈가 있는 쿼리 전체
  SUBSTRING(dest.text,der.statement_start_offset/2 + 1,   
             (CASE WHEN der.statement_end_offset = -1   
                   THEN LEN(CONVERT(nvarchar(max), dest.text)) * 2   
                   ELSE der.statement_end_offset end -  
                        der.statement_start_offset  
             )/2  
             ) AS query_text              -- 프로시저의 경우 어느 쿼리에서 문제가 발생하는지 확인
from sys.dm_tran_locks dtl
  left outer join  sys.dm_exec_requests der
     on der.session_id=dtl.request_session_id
  outer apply sys.dm_exec_Sql_text(der.sql_handle) dest
where resource_database_id != 32767 and resource_database_id > 4
OPTION (MAXDOP 1)

58번 세션이 54번 세션에 의해 블락킹을 당했고, 3600 초 동안 수행이 되고 있네요.

2. 다른 자원에는 문제가 없는지?

CPU, 메모리, 네트워크 부분에서 특이점이 없는지 확인합니다.

이 부분은 포스팅에서는 패스하고, 디스크 부분을 확인해 보겠습니다.

( CPU, 메모리 사용률이나 Page Life Expectancy 값 등을 확인해 보시면 될것 같네요. )

디스크 부분을 보면

Disk Writes/sec , Disk Queue Length, Latch Waits 수치가 증가한 모습을 보입니다.

그러나 Disk sec/Writes 는 크게 증가하지 않은 모습으로 나왔습니다.

지표	의미
Latch Waits/sec	초당 래치를 획득하기 위해 대기하는 수
Disk Writes/sec	초당 디스크 쓰기 횟수
Disk Queue Length	디스크 큐에 있는 길이 ( =대기열 ),  2이상이면 지연이 발생할 수 있는 가능성이 있음
Disk sec/Write	쓰기에 걸리는 시간

래치 대기가 있었는지 확인해 봅니다.

Select wait_type,    -- EX : 래치 요청이 배타 모드
                     -- SH : 래치 요청이 공유 모드
                     -- UP : 래치 요청이 갱신 모드
       waiting_tasks_count,    -- 유형의 대기 수
       wait_time_ms,           -- 유형의 총 대기 시간 (밀리초)
       max_wait_time_ms,       -- 유형의 최대 대기 시간
       signal_wait_time_ms     -- WAIT 스레드가 요청 받은 시간과 실행을 시작한 사이의 차이시간
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
option (maxdop 1)

PAGEIOLATCH_EX. 즉 PAGE IO 를 하기위한 배타적 래치 요청이 67 회 1초가량 지연이 된것을 확인할 수 있습니다.

( 지표는 라이브였고, 이 후 테스트 환경에서 쿼리 수행한거라 값은 많이 다릅니다. )

그럼 PAGE IO 지연과 관련된 파일은 어떤 것일까?

select
  DB_NAME(divfs.database_id) AS DATABASE_NAME,
  mf.physical_name,
  dipir.io_type,                            -- 보류 중인 I/O 요청의 유형 ( 예: DISK )
  divfs.io_stall,                           -- 파일에 대한 I/O가 완료될 때까지 대기한 총 시간(밀리초)
  dipir.io_pending_ms_ticks                -- 큐에서 대기중인 개별 I/O의 총 시간(밀리초)
from sys.dm_io_virtual_file_stats(NULL, NULL) divfs
  inner join sys.dm_io_pending_io_requests as dipir
    on divfs.file_handle = dipir.io_handle
  inner join sys.master_files mf
    on divfs.database_id = mf.database_id and divfs.file_id = mf.file_id 
OPTION (MAXDOP 1)

보류 중인 I/O 요청은 DISK 이고 총 57343 밀리초 동안 지연이 발생한 것으로 보입니다.

그러나 테스트 환경이다 보니 마찬가지로 큐에서 대기중인 개별 I/O 의 시간은 0으로 나왔습니다.

큐에서 대기하는 I/O 지연을 확인하기 위해서는 io_pending_ms_ticks 컬럼도 확인해 주셔야 합니다.

결론

---

이렇게 어떤 트랜잭션에서 Lock 을 획득하고 나서 해제하고 있지 않은지,

어떤 세션들이 얼마만큼 대기하고 있는지에 대해서 정리해 보았습니다.

또한 Latch 는 어느 파일에서 발생하고 있는지, 디스크는 밀림 현상이 없는지에 대해서도 함께 파악해 보았습니다.

( !! 물론 저렇게 출력된 수치들은 환경마다 기준치가 다를 수 있습니다. )

이 결론을 토대로, 쿼리에서 이슈가 있다면 쿼리를 해결하는 방향으로

디스크가 밀린다면  디스크 컨트롤러나 디스크를 더 좋은 것으로 교체하는 방향으로 잡으면 좋을 것 같습니다.