[SQL Server / MS-SQL] Wait Stats (대기 통계)

 

---

 

SQL Server 의 Wait Stats(대기 통계) 는 쿼리가 대기하는 시간을 측정하여 성능 병목 현상을 식별하는 데 중요한 역할을 한다.
Wait Stats 에는 다양한 유형들이 있는데, 주로 확인해야 할 유형에 대해 살펴보자.

 

Wait Stats (대기 통계) 유형

 

1. PAGEIOLATCH_

디스크에 있는 데이터를 메모리로 로드할 때 발생하는 대기 시간이다. SQL Server는 데이터를 메모리에 로드하여 작업을 수행하는데, 이 과정에서 디스크 I/O가 필요하다. 이 때 디스크에서 데이터를 읽어 메모리로 가져오는 동안 발생하는 대기 시간이다.

PAGEIOLATCH 유형

• PAGEIOLATCH_SH (Shared)
  • 읽기 작업을 위해 페이지를 공유 모드로 로드할 때의 대기 시간이다.
  • 주로 읽기 작업에서 발생하며, 여러 세션이 동시에 동일한 페이지를 읽을 때 발생할 수 있다.
• PAGEIOLATCH_EX (Exclusive)
  • 쓰기 작업을 위해 페이지를 배타 모드로 로드할 때의 대기 시간이다
  • 주로 쓰기 작업에서 발생하며, 페이지를 수정하기 위해 잠금을 걸 때 발생한다.
• PAGEIOLATCH_UP (Update)
  • 업데이트 작업을 위해 페이지를 업데이트 모드로 로드할 때의 대기 시간이다.
  • 페이지를 업데이트할 때 발생하며, 업데이트 작업을 위해 페이지를 잠글 때 발생한다.

 

2. LCK_M_

트랜잭션 실행 후, 다른 트랜잭션이 해당 리소스의 LOCK 을 가지고 있고, 이 LOCK 이 해제될 때까지 기다리는 시간이다.
LCK_M_ 대기 유형은 주로 대기 시간이 길수록 동시성 문제를 나타내며, 특정 자원에 대한 경합이 발생할 때 나타난다.

LCK_M_ 유형

• LCK_M_S (Shared Lock)
  • 공유 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 읽기 작업에서 주로 발생하며, 이는 다른 트랜잭션이 해당 리소스에 배타적 잠금을 가지고 있을 때 발생한다.
• LCK_M_U (Update Lock)
  • 업데이트 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 업데이트 작업을 준비할때 발생하며, 다른 트랜잭션이 해당 리소스에 공유 잠금 또는 배타적 잠금을 가지고 있을 때 발생한다.
• LCK_M_X (Exclusive Lock)
  • 배타적 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 쓰기 작업에서 주로 발생하며, 다른 트랜잭션이 해당 리소스에 공유 잠금, 업데이트 잠금 또는 배타적 잠금을 가지고 있을 때 발생한다.
• LCK_M_IS (Intent Shared Lock)
  • 의도 공유 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 데이터베이스 또는 테이블 수준에서 잠금을 설정하며, 다른 트랜잭션이 해당 리소스에 배타적 잠금을 가지고 있을 때 발생한다.
• LCK_M_IX (Intent Exclusive Lock)
  • 의도 배타적 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 데이터베이스 또는 테이블 수준에서 잠금을 설정하며, 다른 트랜잭션이 해당 리소스에 공유 잠금 또는 배타적 잠금을 가지고 있을 때 발생한다.
• LCK_M_SCH_S (Schema Stability Lock)
  • 스키마 안정성 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 스키마가 변경되지 않도록 보장하며, 다른 트랜잭션이 스키마 변경 잠금을 가지고 있을 때 발생한다.
• LCK_M_SCH_M (Schema Modification Lock)
  • 스키마 변경 잠금이 걸릴 때 발생하는 대기 시간이다.
  • 스키마를 변경하는 동안 다른 트랜잭션이 해당 스키마에 접근하지 못하게 하며, 다른 트랜잭션이 스키마 안정성 잠금을 가지고 있을 때 발생한다.
    
    

 

3. WRITELOG

트랜잭션 로그를 디스크에 기록할 때 발생하는 대기 시간이다.
WRITELOG 대기 유형은 주로 트랜잭션이 커밋될 때 발생하며, 이 대기 유형은 주로 로그 디스크의 I/O 병목을 나타낸다.

 

4. ASYNC_NETWORK_IO

SQL Server 에서 클라이언트로 데이터를 전송할 때 발생하는 대기 시간이다.
이는 클라이언트가 데이터를 수신할 수 있을때까지 기다리는 시간이며, 이 대기 유형은 주로 네트워크 성능 문제 또는 클라이언트 응용 프로그램의 처리 성능 문제를 나타낸다.

 

5. CXPACKET

병렬 쿼리 실행 중 발생하는 대기 시간이다. 이는 병렬 쿼리 실행 시 서로 다른 스레드가 서로의 작업이 완료되기를 기다릴 때 발생하며, 이 대기 유형은 주로 병렬 처리와 관련된 문제가 있을 때 발생한다.
과도한 병렬 처리 시, 서버의 MAXDOP(Max Degree of Parallelism) 설정을 적절히 조정하여 조정할 수 있다.

 

6. SOS_SCHEDULER_YIELD

SQL Server 는 내부적으로 스케줄러를 사용하여 작업 스레드를 관리하게 되는데 각 스레드는 일정 시간이 지나면 다른 스레드에게 CPU를 양보해야한다. 이 때, 작업 스레드를 잠시 멈추고 다른 스레드에 CPU 할당할 때 발생하는 대기 시간이다. 높은 SOS_SCHEDULER_YIELD 가 지속되면, CPU 경합 문제가 발생하고 있음을 나타낸다.

SOS_SCHEDULER_YIELD 문제 해결 방안

1) 쿼리 최적화
 - CPU 사용량이 높은 쿼리를 식별하고, 이를 최적화한다.

2) CPU 자원 증설
 - 서버의 CPU 코어 수를 늘려 더 많은 스레드가 동시에 실행될 수 있도록 한다.

3) 작업 분산
 - 긴 실행 시간의 작업을 여러 개의 작은 작업으로 나누어 CPU 사용을 분산시킨다.

 

7. HADR_SYNC_COMMIT

Always On 가용성 그룹(AG) 에서 발생하는 대기 유형이다. 이는 주 데이터베이스의 트랜잭션이 보조 데이터베이스에 완전히 Commit(동기화) 될 때까지 대기하는 시간이다.