대기타입의 정의 (wait_type)

동적 관리 뷰 중 특정 리소스에서 대기 중인 태스크의 대기 큐에 대한 정보를 반환하는 DM_OS_WAITING_TASKS 를 조회하면 아래와 같은 결과가 나타납니다.

 

SELECT * FROM SYS.DM_OS_WAITING_TASKS WHERE SESSION_ID > 50

여기에서 우리는 wait_type 이라는 컬럼을 볼 수 있는데 여기에 나올 수 있는 항목에 대한 내용을 짚어보고자 합니다.

MSDN에 나와있는 설명은 단지 "대기 유형의 이름" 이라고만 정의되어 있습니다.

열 이름	데이터 형식	설명
waiting_task_address	varbinary(8)	대기 중인 태스크의 주소입니다.
session_id	smallint	태스크와 연결된 세션의 ID입니다.
exec_context_id	int	태스크와 연결된 실행 컨텍스트의 ID입니다.
wait_duration_ms	int	이 대기 유형에 대한 총 대기 시간(밀리초)입니다. 이 시간은 signal_wait_time을 포함합니다.
wait_type	nvarchar(60)	대기 유형의 이름입니다.
resource_address	varbinary(8)	태스크가 대기 중인 리소스의 주소입니다.
blocking_task_address	varbinary(8)	현재 이 리소스를 보유하고 있는 태스크입니다.
blocking_session_id	smallint	요청을 차단하고 있는 세션의 ID입니다. 이 열이 NULL이면 요청이 차단되지 않거나 차단 세션의 세션 정보를 사용할 수 없습니다(또는 식별할 수 없음). -2 = 분리된 분산 트랜잭션이 차단 리소스를 소유합니다. -3 = 지연된 복구 트랜잭션이 차단 리소스를 소유합니다. -4 = 내부 래치 상태 전환 때문에 차단 래치 소유자의 세션 ID를 확인할 수 없습니다.
blocking_exec_context_id	int	차단 태스크의 실행 컨텍스트 ID입니다.
resource_description	nvarchar(1024)	사용 중인 리소스에 대한 설명입니다. 자세한 내용은 아래 목록을 참조하십시오.

dbcc sqlperf(waitstats, clear)

dbcc sqlperf(waitstats)

구글링의 힘을 빌어 대기 유형의 정의를 찾아본 결과는 아래와 같습니다.

[CXPACKET]
Parallel Process 대기. Parallel Query실행 시 각 Process가 실행되는 구간에 Lock Issue가 있을 수도 있음을 의미하거나 특정 하드웨어의 병목으로 인해 다른 Process가 대기하고 있는 Process가 끝날 때까지 대기하고 있을 수도 있음을 의미한다. 

[PAGEIOLATCH_SH]
SELECT 쿼리 시 Shared Latch를 얻기 위한 대기.(디스크에서 메모리로 캐시를 하는 작업)
[PAGEIOLATCH_DT]
Delete 작업이 IO요청에 있는 버퍼를 Latch에서 대기중.
[PAGEIOLATCH_EX]
배타모드에서 IO요청에 있는 버퍼를 Latch에서 대기중.
[PAGEIOLATCH_KP]
유지모드에서 IO요청에 있는 버퍼를 Latch에서 대기중.
[PAGEIOLATCH_NL]
Null모드에서 IO요청에 있는 버퍼를 Latch에서 대기중.
[PAGEIOLATCH_UP]
Update작업에서 IO요청에 있는 버퍼를 Latch에서 대기중.

[PAGEIOLATCH_SH]
SELECT 쿼리 시 Shared Latch를 얻기 위한 대기.IO가 없는 경우
[PAGEIOLATCH_DT]
Delete작업시의 Latch 대기. IO가 없는 경우
[PAGEIOLATCH_EX]
배타모드에서 Latch 대기. IO가 없는 경우
[PAGEIOLATCH_KP]
유지모드에서 Latch 대기. IO가 없는 경우
[PAGEIOLATCH_NL]
Null모드에서 Latch 대기. IO가 없는 경우
[PAGEIOLATCH_UP]
Update작업에서 Latch 대기. IO가 없는 경우

[WRITELOG]
로그를 쓰기 위해 대기하고 있는 시간. Disk의 쓰기 성능에 직접영향을 미친다. Temp Table을 생성하여 Temp Table에 Insert, Delete, Update 작업을 하거나 Table에 이러한 작업이 많이 일어난다면 병목지점이 될 수 있다. 

[LATCH_EX]
배타적인 LATCH를 얻기 위한 요청

[PAGEIOLATCH_EX]
LATCH_EX 대기 유형과 함께 일어나는 요소. 

[NETWORKIO]
Network I/O. NIC의 Bandwidth를 체크해야 한다. 

[LCK_M_S]
Latch Lock. Latch를 얻은 동안에는 다른 프로세스가 페이지의 변경 등을 하지 못하도록 Lock을 걸게 되는 것이다. 그러므로 Latch를 얻고 Latch를 해제할 때까지의 시간이 빠르면 빠를수록 성능은 증가된다. 즉, Memory와 Disk간에 데이터 전송이 빠르다면 LCK_M_S, PAGEIOLATCH_SH의 대기 시간은 줄어들 것이다. 

[IO_COMPLETION]
I/O요청완료. Query Plan이 나쁜 경우 발생하는 대기 유형으로 Full Scan, Index Scan이 일어나는 쿼리에 대한 Query Plan 조정이 필요하다.