데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

  • 병렬 처리 대기 확인

     

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

 

지난 아티클에서 실행 요청을 기다리는 작업에 대해 확인 하는 방법에 대해서 알아 보았다.

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

http://sqlmvp.kr/140207603501

 

 

또 다른 예제를 한번 살펴 보자.

 

  • 세션 53의 상태는 COMMIT TRANSACTION 작업을 기다리고 있다.
  • 세션 54의 상태는 SELECT 작업으로 세션 55에 의해 대기 하고 있다.
  • 세션 55의 상태는 SELECT 작업으로 세션 53에 의해 대기 하고 있다.
  • 세션 56의 상태는 SELECT 작업으로 세션 55에 의해 대기하고 있다.
  • 세션 57의 상태는 SELECT 작업으로 세션 55에 의해 다기하고 있다.

 

이 예제에서는 실제로 모든 요청이 일시 중단(54, 55, 56, 57세션이 모두 키 잠금(KEY: 5:72057594039369728 (ac11a2bc89a9)) 리소스를 획득하려고 대기)되어 있는 상태로 서버는 아무것도 하지 않고 있다. Wait_time 컬럼을 보면 세션 53의 작업이 디스크에 플러시 될 때까지 167ms 대기가 발생한다.

 

중요한 것은 긴 시간 동안 대기가 발생하는 작업이나 짧은 대기지만 반복적으로 나타나는 경우이다. 긴 시간 동안의 대기는 일반적으로 잠금이 발생 한다. DMV sys.dm_os_wating_tasks를 사용하여 현재 대기 중인 태스크를 확인 할 수 있다. 이 DMV는 sys.dm_exec_requests 와 유사하지만 더 많은 정보를 가지고 있다.

 

select * from sys.dm_os_waiting_tasks

where session_id >= 50

and session_id <> @@spid;

 

 

  • 세션 53의 상태는 로그 플러시를 기다리고 있다.
  • 세션 57의 상태는 행 잠금을 획득하기 위한 대기로 세션 53에 의해 40ms 동안 차단당하고 있다.
  • 세션 54의 상태는 행 잠금을 획득하기 위한 대기로 세션 57에 의해 차단된다. 하지만 세션 57이 세션 53에 의해 차단되어 있는 상태이다.

 

병렬 쿼리의 작업은 별도의 CPU 코어에서 실행 할 수 있다. 이 요청 작업은 기본적으로 생산자 – 소비자 큐를 이용하여 통신한다. 병렬에 의한 대기 유형은 CXPACKET 타입으로 나타난다.

 

다음 스크립트를 실행하여 대기 정보를 확인해 보면 현재 병렬로 인한 대기가 발생하는 것을 확인 할 수 있다.

select r.session_id,

    status,

    command,

    r.blocking_session_id,

    r.wait_type as [request_wait_type],

    r.wait_time as [request_wait_time],

    t.wait_type as [task_wait_type],

    t.wait_duration_ms as [task_wait_time],

    t.blocking_session_id,

    t.resource_description

from sys.dm_exec_requests r

left join sys.dm_os_waiting_tasks t

    on r.session_id = t.session_id

where r.session_id >= 50

and r.session_id <> @@spid;

 

 

세션 54는 3개의 작업 대기를 가지고 있으며 잠금 타입은 CXPACKET이다. 병렬 작업 중 하나가 페이지 잠금을 기다리고 있어 대기가 발생한다.

 

 

 

[참고자료]

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
1950 성능분석 16탄 – 메모리 / CPU 관련 성능 카운터 jevida(강성욱) 2016.10.15 5064
1949 성능분석 15탄 – I/O 관련 성능 카운터 jevida(강성욱) 2016.10.15 4001
1948 성능분석 14탄 – SQL Server 사용 성능 카운터 jevida(강성욱) 2016.10.15 3952
1947 성능분석 13탄 – 누락된 인덱스(missing index) jevida(강성욱) 2016.10.15 2343
1946 성능분석 12탄 – 문제 쿼리 식별 jevida(강성욱) 2016.10.15 2122
1945 성능분석 11탄 – 실행 계획 분석 jevida(강성욱) 2016.10.15 4144
1944 성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent) jevida(강성욱) 2016.10.15 1909
1943 성능분석 9탄 – 쿼리 실행 분석 jevida(강성욱) 2016.10.15 3866
1942 성능분석 8탄 – IO 통계 (DISK 활동 분석) jevida(강성욱) 2016.10.15 1459
1941 성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER jevida(강성욱) 2016.10.15 1590
1940 성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형 jevida(강성욱) 2016.10.15 1882
1939 성능분석 5탄 – 메모리 및 네트워크 관련 대기 유형 jevida(강성욱) 2016.10.15 1927
1938 성능분석 4탄 – 디스크 및 IO 관련 대기 유형 jevida(강성욱) 2016.10.15 2066
1937 성능분석 3탄 – 집계 대기 통계 jevida(강성욱) 2016.10.15 1916
» 성능분석 2탄 – 실행 요청을 기다리는 작업 확인 및 분석 (병렬 처리 대기 확인) jevida(강성욱) 2016.10.15 1352
1935 성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 jevida(강성욱) 2016.10.15 1980
1934 확장이벤트를 사용하여 데드락 정보 확인 jevida(강성욱) 2016.10.15 1548
1933 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2114
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1761
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2050





XE Login