성능분석 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/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34970
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17247
1954 FileStream Garbage Collection jevida(강성욱) 2016.10.18 3502
1953 프로시저 캐시에서 중복 쿼리 계획 확인 jevida(강성욱) 2016.10.18 3753
1952 Sp_reset_connection jevida(강성욱) 2016.10.18 3810
1951 성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터 jevida(강성욱) 2016.10.15 3806
1950 성능분석 16탄 – 메모리 / CPU 관련 성능 카운터 jevida(강성욱) 2016.10.15 6232
1949 성능분석 15탄 – I/O 관련 성능 카운터 jevida(강성욱) 2016.10.15 4090
1948 성능분석 14탄 – SQL Server 사용 성능 카운터 jevida(강성욱) 2016.10.15 4069
1947 성능분석 13탄 – 누락된 인덱스(missing index) jevida(강성욱) 2016.10.15 2464
1946 성능분석 12탄 – 문제 쿼리 식별 jevida(강성욱) 2016.10.15 2285
1945 성능분석 11탄 – 실행 계획 분석 jevida(강성욱) 2016.10.15 4303
1944 성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent) jevida(강성욱) 2016.10.15 2006
1943 성능분석 9탄 – 쿼리 실행 분석 jevida(강성욱) 2016.10.15 4236
1942 성능분석 8탄 – IO 통계 (DISK 활동 분석) jevida(강성욱) 2016.10.15 1520
1941 성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER jevida(강성욱) 2016.10.15 1786
1940 성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형 jevida(강성욱) 2016.10.15 1972
1939 성능분석 5탄 – 메모리 및 네트워크 관련 대기 유형 jevida(강성욱) 2016.10.15 2163
1938 성능분석 4탄 – 디스크 및 IO 관련 대기 유형 jevida(강성욱) 2016.10.15 2363
» 성능분석 3탄 – 집계 대기 통계 jevida(강성욱) 2016.10.15 2017
1936 성능분석 2탄 – 실행 요청을 기다리는 작업 확인 및 분석 (병렬 처리 대기 확인) jevida(강성욱) 2016.10.15 1446
1935 성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 jevida(강성욱) 2016.10.15 2139





XE Login