성능분석 3탄 – 집계 대기 통계

성능분석 3탄 – 집계 대기 통계

 

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

 

SQL Server 집계 통계(Aggregates Statistics)는 DMV 중 sys.dm_os_wait_stats을 통해서 확인 할 수 있다. sys.dm_os_wait_stats에서는 현재 실행 중인 요청 및 대기 작업을 보고 어떤 순간에 대기하고 있는지 보여주며 서버가 시작된 이후 누적된 값을 제공한다.

 

DMV를 통하여 대기 통계를 확인하는 것은 간단하지만 결과를 해석하는 방법에는 약간 까다롭다.

 

select * from sys.dm_os_wait_stats order by wait_time_ms desc;

 

 

 

결과를 살펴 보면 상단에 위치한 목록에 DIRTY_PAGE_POOL, REQUEST_FOR_DEADLOCK_SEARCH, LAZYWRITER_SLEEP등의 유형을 확인 할 수 있다. 하지만 여기에는 우리가 보려고 하는 대기가 보이지 않는다. session_id >= 50 조건을 사용하여 백그라운드 작업과 내부 스레드 작업을 필터 할 수 있다.

 

많은 백그라운드 작업의 패턴은 대기 – 실행 – 대기로 반복 된다. 다음 스크립트는 백그라운드 작업의 목록을 필터 하여 대기 정보를 확인 할 수 있다.

select * from sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN', N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE', N'TRACEWRITE', N'XE_DISPATCHER_WAIT', N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER', N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP') order by wait_time_ms desc;

 

 

이제 우리는 대기 정보를 좀더 일관성 있게 확인 할 수 있다. 이 결과는 병목 현상의 원인을 확인 하기 위해 중요한 단계가 될 수 있다. 하지만 결과 값에 따른 해석은 매우 주관적 일 수 있다.

현재 가장 많은 대기 값을 보여주는 WRITELOG의 4636805 밀리세컨드의 값은 좋을까 나쁠까? 이에 대해 답을 할 수가 없다. 우선 wait_time_ms 값은 누적 값이기 때문에 계속적으로 증가 할 것이다. max_wait_time_ms 컬럼에서 1401 밀리세컨드로 로그를 플러시 하는데 최대 1.4초를 기다려야 했다는 것을 확인 할 수 있다. 또한 waiting_tasks_count 값을 통하여 한 번 이상 대기가 발생 했다는 것을 확인 할 수도 있다. 여기에서 wait_time_ms / wait_tasks_count 값을 계산하면 평균 대기 시간을 산출 할 수 있다.

 

상위 대기 목록으로는 잠금 관련 대기(LCK_M_S, LCK_M_IS, LCK_M_IX)를 확인 할 수 있다. 대부분이 대기는 잠금이며 주요 이슈로 잠금 경합인 CXPACKET 대기 유형을 볼 수 있다.

 

집계 또는 개별 쿼리에 대해 대기 유형을 조사하고 무엇을 의미하는지 이해하는 것은 매우 중요하다. 대기 유형에 대한 설명은 다음 문서를 참고 한다.

• Performance Tuning Waits Queues.doc :

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-

 

 

[참고자료]

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

 

2014-03-11 / 강성욱 / http://sqlmvp.kr