대기 통계 설명
서버 성능을 모니터링하는 포괄적인 접근 방식에는 서버가 대기 중인 내용을 평가하는 작업이 포함됩니다. 대기 통계는 복잡하며 SQL Server에는 실행 중인 각 스레드를 모니터링하고 스레드가 대기 중인 내용을 기록하는 수백 가지 대기 유형이 있습니다.
SQL Server 성능 문제를 효과적으로 감지하고 해결하려면 대기 통계가 작동하는 방식과 데이터베이스 엔진이 요청을 처리하는 동안 이를 활용하는 방법을 이해해야 합니다. 이 지식을 통해 병목 상태를 정확히 파악하고 성능을 보다 정확하게 최적화할 수 있습니다.
대기 통계는 리소스 대기, 큐 대기 및 외부 대기의 세 가지 유형의 대기로 세분화 됩니다.
- 리소스 대기는 SQL Server의 작업자 스레드가 현재 스레드에서 사용 중인 리소스에 대한 액세스를 요청할 때 발생합니다. 리소스 대기의 예로는 잠금, 래치, 네트워크 및 디스크 I/O 대기가 있습니다.
- 작업자 스레드가 유휴 상태이고 작업이 할당되기를 기다리는 경우 큐 대기가 발생합니다. 큐 대기의 예로는 교착 상태 모니터링 및 삭제된 레코드 정리가 있습니다.
- SQL Server가 연결된 서버 쿼리와 같은 외부 프로세스에서 완료되기를 기다리는 경우 외부 대기가 발생합니다. 외부 대기의 예로는 클라이언트 애플리케이션에 많은 결과 세트를 반환하는 데 관련된 네트워크 대기가 있습니다.
sys.dm_os_wait_stats 시스템 보기를 확인하여 실행된 스레드에서 발생하는 모든 대기 및 Azure SQL Database용 sys.dm_db_wait_stats를 탐색할 수 있습니다. sys.dm_exec_session_wait_stats 시스템 보기에는 활성 대기 세션이 나열됩니다.
이러한 시스템 보기를 사용하면 서버 성능에 대한 개요를 확인하고 구성 또는 하드웨어 문제를 쉽게 식별할 수 있습니다. 이 데이터는 인스턴스 시작 시점부터 지속되지만 변경 내용을 관찰하기 위해 필요한 대로 데이터를 지울 수 있습니다.
대기 통계는 서버에서 전체 대기의 백분율로 평가됩니다.
이 쿼리 sys.dm_os_wait_stats 의 결과는 대기 유형 및 대기 시간 백분율(대기 비율 열)의 집계 및 각 대기 유형에 대한 평균 대기 시간(초)을 보여 줍니다.
이 경우 서버에는 REDO_THREAD_PENDING_WORK 및PARALLEL_REDO_TRAN_TURN 대기 유형으로 표시된 대로 Always On 가용성 그룹이 있습니다. CXPACKET 및 SOS_SCHEDULER_YIELD 대기의 상대적으로 높은 비율은 이 서버가 일부 CPU 압력을 받고 있음을 나타냅니다.
DMV는 지난 SQL Server 시작 이후 누적된 가장 높은 시간 동안 대기 유형 목록을 제공하므로, 대기 통계 데이터를 정기적으로 수집하고 저장하면 성능 문제를 이해하고 다른 데이터베이스 이벤트와 상호 연결하는 데 도움이 될 수 있습니다.
DMV가 마지막 SQL Server 시작 이후 누적된 가장 높은 시간을 가진 대기 유형 목록을 제공한다는 점을 고려하면 대기 통계를 정기적으로 수집하고 저장하면 성능 문제를 이해하고 다른 데이터베이스 이벤트와 상호 연결하는 데 도움이 될 수 있습니다.
SQL Server에서 몇 가지 유형의 대기를 사용할 수 있지만 그 중 일부는 일반적입니다.
RESOURCE_SEMAPHORE - 특정 쿼리에 대한 과도한 메모리 부여로 인해 쿼리가 메모리를 사용할 수 있길 기다리고 있음을 나타냅니다. 이 문제는 일반적으로 긴 쿼리 런타임 또는 시간 제한으로 나타납니다. 이러한 대기 유형의 원인에는 오래된 통계, 누락된 인덱스 및 높은 쿼리 동시성이 포함될 수 있습니다.
LCK_M_X - 차단 문제를 자주 나타냅니다. 이 문제는 격리 수준으로 변경
READ COMMITTED SNAPSHOT하거나, 인덱싱을 최적화하여 트랜잭션 시간을 줄이거나, T-SQL 코드 내에서 트랜잭션 관리를 개선하여 해결할 수 있습니다.PAGEIOLATCH_SH - 이 대기 유형은 인덱스에 문제가 있거나 유용한 인덱스가 없으므로 SQL Server가 과도한 양의 데이터를 검색하도록 할 수 있습니다. 또는 대기 수가 낮지만 대기 시간이 높으면 스토리지 성능 문제가 발생할 수 있습니다. 시스템 뷰의 데이터
waiting_tasks_count와wait_time_ms열을sys.dm_os_wait_stats분석하여 지정된 대기 유형의 평균 대기 시간을 계산하여 이 동작을 관찰할 수 있습니다.SOS_SCHEDULER_YIELD - 이 대기 유형은 높은 CPU 사용률을 나타낼 수 있으며, 이는 높은 수의 대용량 검색 또는 누락된 인덱스와 상관 관계가 있으며 종종 CXPACKET 대기 수가 많은 경우가 많습니다.
CXPACKET - 이 대기 유형이 많이 발생하면 잘못된 구성을 나타낼 수 있습니다. SQL Server 2019 이전에는 MAXDOP(최대 병렬 처리 수준)에 대한 기본 설정은 쿼리에 사용 가능한 모든 CPU를 사용하는 것이었습니다. 또한 병렬 처리에 대한 비용 임계값이 5로 설정되어 작은 쿼리가 병렬로 실행되어 처리량이 제한될 수 있습니다. 이 대기 유형을 줄이려면 MAXDOP 설정을 낮추고 병렬 처리에 대한 비용 임계값을 늘릴 수 있습니다. 그러나 CXPACKET 대기 유형은 일반적으로 인덱스 튜닝을 통해 해결되는 높은 CPU 사용률을 나타낼 수도 있습니다.
PAGEIOLATCH_UP - 데이터 페이지 2:1:1 의 이 대기 유형은 PFS(페이지 사용 가능한 공간) 데이터 페이지에서 TempDB 경합을 나타낼 수 있습니다. 각 데이터 파일에는 약 64MB의 데이터당 하나의 PFS 페이지가 있습니다. 이 대기는 일반적으로 SQL Server 2016 이전과 같이 TempDB 파일이 하나만 있기 때문에 발생합니다. 기본 동작은 TempDB에 하나의 데이터 파일을 사용하는 것이었습니다. TempDB의 모범 사례는 CPU 코어당 최대 8개의 파일을 사용하는 것입니다. TempDB 데이터 파일이 균등하게 사용될 수 있도록 해당 파일의 크기가 같고 자동 증가 설정이 동일한지 확인하는 것이 중요합니다. SQL Server 2016 이상에서는 동시에 일관되게 증가되도록 TempDB 데이터 파일의 증가를 제어합니다.
앞서 언급한 DMV 외에도 쿼리 저장소 는 특정 쿼리와 관련된 대기도 추적합니다. 쿼리 저장소에서 추적하는 대기 데이터는 DMV의 데이터만큼 세분화되지는 않지만 쿼리가 대기 중인 내용에 대한 유용한 개요를 제공합니다.