성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형

성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형

 

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

 

SQL Server에서 발생하는 대기 유형 중 CPU 경합 및 동시성 관련 대기 유형에 대해서 살펴 본다.

 

LCK_*

LCK는 잠금으로 인한 대기 정지 작업을 나타낸다. LCK_M_S* 대기 유형은 데이터를 읽기 위해(공유 잠금) 잠금을 획득하려고 할 때 발생 한다. LCK_M_SCH* 대기 유형은 객체의 스키마 잠금을 표시한다.

대기 유형	설명
LCK_M_BU	태스크가 대량 업데이트(BU) 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_IS	태스크가 내재된 공유(IS) 잠금을 획득하려고 대기하는 경우에 발생한다
LCK_M_IU	태스크가 의도 업데이트(IU) 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_IX	태스크가 의도 배타(IX) 잠금을 획득하려고 대기하는 경우에 발생한다
LCK_M_RIn_NL	태스크가 현재 키 값의 NULL 잠금 및 현재 키와 이전 키 간의 삽입 범위 잠금을 획득하려고 대기하는 경우에 발생한다
LCK_M_RIn_S	태스크가 현재 키 값의 공유 잠금 및 현재 키와 이전 키 간의 삽입 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RIn_U	태스크가 현재 키 값의 업데이트 잠금 및 현재 키와 이전 키 간의 삽입 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RIn_X	태스크가 현재 키 값의 배타 잠금 및 현재 키와 이전 키 간의 삽입 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RS_S	태스크가 현재 키 값의 공유 잠금 및 현재 키와 이전 키 간의 공유 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RS_U	태스크가 현재 키 값의 업데이트 잠금 및 현재 키와 이전 키 간의 업데이트 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RX_S	태스크가 현재 키 값의 공유 잠금 및 현재 키와 이전 키 간의 배타 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_RX_X	태스크가 현재 키 값의 배타 잠금 및 현재 키와 이전 키 간의 배타 범위 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_S	태스크가 공유 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_SCH_M	태스크가 스키마 수정 잠금을 획득하려고 대기하는 경우에 발생한다
LCK_M_SCH_S	태스크가 스키마 공유 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_SIU	태스크가 의도 업데이트 공유 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_SIX	태스크가 의도 배타 공유 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_U	태스크가 업데이트 잠금을 획득하려고 대기하는 경우에 발생한다.
LCK_M_UIX	태스크가 의도 배타 업데이트 잠금을 획득하려고 대기하는 경우에 발생한다
LCK_M_X	태스크가 배타 잠금을 획득하려고 대기하는 경우에 발생한다.

 

 

PAGELATCH_*

PAGEIOLATCH_* 대기 유형과 혼돈하지 말아야 한다. 이 대기 시간이 높은 경우에는 데이터베이스에서 매우 자주 업데이트되는 데이터의 영역을 나타낸다.

대기 유형	설명
PAGELATCH_DT	태스크가 I/O 요청에 없는 버퍼를 래치에서 대기하는 경우에 발생한다. 래치 요청이 삭제 모드에 있다.
PAGELATCH_EX	태스크가 I/O 요청에 없는 버퍼를 래치에서 기다리는 경우에 발생한다. 래치 요청이 배타 모드에 있다.
PAGELATCH_KP	태스크가 I/O 요청에 없는 버퍼를 래치에서 대기하는 경우에 발생한다. 래치 요청이 유지 모드에 있다.
PAGELATCH_NL	정보를 제공하기 위해서만 확인된다. 향후 호환성은 보장되지 않습니다
PAGELATCH_SH	태스크가 I/O 요청에 없는 버퍼를 래치에서 대기하는 경우에 발생한다. 래치 요청이 공유 모드에 있다.
PAGELATCH_UP	태스크가 I/O 요청에 없는 버퍼를 래치에서 대기하는 경우에 발생한다. 래치 요청이 업데이트 모드에 있다.

 

 

LATCH_*

SQL Server 내부 리소소의 경합을 나타낸다. DMV Sys.dm_os_latch_stats를 사용하여 latch_class 세부사항을 확인 할 수 있다.

select * from Sys.dm_os_latch_stats

 

 

대기 유형	설명
LATCH_DT	DT(삭제) 래치를 대기하는 경우에 발생한다. 버퍼 래치 또는 트랜잭션 표시 래치를 포함하지 않는다.
LATCH_EX	EX(배타) 래치를 대기하는 경우에 발생한다. 버퍼 래치 또는 트랜잭션 표시 래치를 포함하지 않는다.
LATCH_KP	KP(유지) 래치를 대기하는 경우에 발생한다. 버퍼 래치 또는 트랜잭션 표시 래치를 포함하지 않는다.
LATCH_NL	정보를 제공하기 위해서만 제공된다. 향후 호환성은 보장되지 않는다.
LATCH_SH	SH(공유) 래치를 대기하는 경우에 발생한다. 버퍼 래치 또는 트랜잭션 표시 래치를 포함하지 않는다.
LATCH_UP	UP(업데이트) 래치를 대기하는 경우에 발생한다. 버퍼 래치 또는 트랜잭션 표시 래치를 포함하지 않는다.

 

 

CMEMTHREAD

작업이 공유 메모리 할당에 액세스 차단이 발생하면서 기다리는 대기 유형으로 태스크가 스레드로부터 안전한 메모리 개체를 기다리는 경우에 발생한다. 여러 태스크가 같은 메모리 개체의 메모리를 할당하려고 하여 경합이 발생할 때 대기 시간이 증가할 수 있다. 즉 동시성과 관련되어 있으며 최신 누적 업데이트 및 서비스팩에서 일부 문제가 해결되어 있다.

 

 

SOS_SCHEDULER_YIELD

다른 작업이 실행될 수 있도록 태스크가 자발적으로 스케줄러를 양보하는 경우에 발생한다. 이 대기 중에 태스크는 해당 퀀텀이 갱신될 때까지 대기한다. 이 대기 유형은 스핀락 경합을 표시할 수 있다. 스핀락은 리소스 보호를 하기 위해 사용되는 SQL Server의 매우 가벼운 대기 잠금 기본 요소 이다. 스핀락이 발생하면 CPU 사용률이 90% ~ 100%가 되는 경우가 많으며 작업이 느려진다. DMV sys.dm_os_sponlock_stats를 사용하여 세부 정보를 확인 할 수 있다.

select * from sys.dm_os_spinlock_stats order by spins desc

 

 

 

RESOURCE_SEMAPHORE_QUERY_COMPILE

동시 쿼리 컴파일 수가 한계에 도달한 경우에 발생한다. 대기 수가 많고 대기 시간이 길면 컴파일, 다시 컴파일 또는 캐싱할 수 없는 계획 수가 과도하게 많은 것이다.

 

 

SQLCLR_QUANTUM_PUNISHMENT

SQL Server엔진 내부의 CLR 코드를 실행할 경우 발생하는 대기 유형이다. CLR코드는 CPU에게 시간을 양보하지 않는다. CLR 태스크가 실행 퀀텀을 초과하여 조절되는 경우에 발생하며 리소스를 많이 사용하는 이 태스크가 다른 작업에 미치는 영향을 줄이기 위해 수행된다.

 

 

[참고자료]

• http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/
• http://msdn.microsoft.com/ko-kr/library/ms179984.aspx

 

 

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