SQL Server 대기 통계 모니터링 : CPU 대기 해석

CPU 관련 SQL Server 대기 통계 해석

주식 중개인은 한 번 숫자에 의해 잘못 인도 되었기 때문에 잘못된 결정을 내렸다고 말했습니다. 실제로 호기심 많은 단어 선택; 마치 그가 스프레드 시트에서 악의적 인 숫자로 속인 무고한 방관자 인 것처럼 말입니다. 모든 종류의 분석에서, 숫자로 표현 된 데이터의 정확한 해석에 대한 책임은 분석가에게 있습니다.

 

이는 경제 분석, 의료 연구 데이터 또는 금융 시장 연구에서와 마찬가지로 SQL Server 성능 분석에서도 중요합니다. 이 글에서는 CPU 대기 통계를 올바르게 해석하여 실제로 문제를 나타내는 지 확인하고 그렇다면 어떤 조치를 취할 수 있는지 확인합니다.

 

SQL Server 대기 통계 모니터링 에 대한 이전 기사에서는 sys.dm_os_wait_stats 의 출력을 보았습니다 .  우리는 signal_wait_time_ms 가 대기 한 리소스가 사용 가능 해지면 스레드를 기다려야하는 밀리 초 수를 나타냅니다. 스레드를 기다리는 데 상당한 시간이 소요되는 경우 시스템 CPU 코어가 수요를 따라 가지 않고 성능 병목 상태라고 쉽게 상상할 수 있습니다. 반드시 그런 것은 아닙니다.

 

일부 저자들은 총 신호 대기 시간이 신호 대기에서 차지하는 총 대기 시간의 비율보다 덜 중요하다고 올바르게 제안했습니다. 이것은 그림의 절반에 불과합니다. 모든 대기에서 신호 대기 시간을 집계하면 성능 병목 현상을 나타내지 않는 높은 비율이 표시됩니다.

 

다음 쿼리는 CPU 관련 대기에 대해 블로그에서 가져 왔습니다.

 

SELECT CAST ( 100.0 * SUM ( signal_wait_time_ms ) / SUM ( wait_time_ms )

 

AS 숫자 ( 20 , 2 )) AS signal_cpu_waits

 

FROM sys . dm_os_wait_stats

 

 

 

이 쿼리를 실행하면 다음과 같은 결과가 표시 될 수 있습니다.

 

전체 신호 대기 백분율

 

 

출처에 따르면, 이것은 약간 높은 비율을 나타냅니다. 개별 신호 대기를 보면 어떻게되는지 봅시다.

 

SELECT *, CAST ( 100.0 * signal_wait_time_ms / NULLIF ( wait_time_ms , 0 )

 

AS DECIMAL ( 5 , 2 )) AS [신호 대기 백분율]

 

개별 대기에 대한 신호 대기 백분율

 

 

처음 세 행에는 신호 대기 시간이 포함되어있을뿐만 아니라 총 대기 시간과 정확히 같은 신호 대기 시간이 포함됩니다. 이것은 내가 "아무것도하지 않는다"라고 부르는 대기에 일반적인 행동입니다. 수행 할 작업이없는 대기에 대한 대기 시간이 종종 많기 때문에 위 쿼리에서 계산 된 비율에 대한 기여도는 불균형 적으로 큽니다. 실제로 누군가 CPU에 문제가 있다고 생각할 수 있습니다. 더 나쁜 것은 실제 CPU 문제가이 부풀려진 비율에 덜 큰 영향을 미쳐 눈에 띄지 않게 될 수 있습니다.

 

목록의 네 번째 대기 유형은 SOS_SCHEDULER_YIELD입니다.이 점을 곧 고려하겠습니다.

 

세 가지 SQL Server 대기 유형

신호 대기 시간과 전체 대기 시간 간의 전체 비율은 수행 할 작업이 모두 필터링 된 경우에만 유용합니다. 이를 수행하기위한 예제 쿼리는 이전 설치에서 제공되었으며 여기에서 다운로드 할 수 있습니다 .

 

SOS_SCHEDULER_YIELD

스레드 실행에 대한 사용자 모드 스케줄링 (UMS)에 대한 세부 사항을 논의 할 시간이나 공간이 현재 없습니다. 매혹적인 주제이기 때문에 너무 나쁩니다. UMS는 실행을 다른 스레드로 전환하는시기와 방법에 따라 응용 프로그램이 운영 체제보다 잘 알고 있다는 개념을 기반으로하므로 운영 체제에 필요한 비싼 커널 컨텍스트 스위치를 줄일 수 있습니다. UMS는 대기 스레드의 우선 순위를 조작하여 운영 체제가 항상 SQL Server가 원하는 스레드에 시간 조각을 제공하도록함으로써이를 수행했습니다. UMS는 SQL Server 7에 도입되었으며 몇 년 동안 SQL Server가이 프로그램을 사용하는 유일한 프로그램이었습니다. 그러나 Microsoft의 Windows 직원은이 아이디어가 좋은 아이디어라고 생각했습니다.

 

여기서 가장 중요한 사실은 SQL Server의 스레드 스케줄러가 항상 양자라고하는 4 밀리 초의 균일 한 타임 슬라이스를 처리한다는 것입니다. 따라서 가장 짧은 작업을 제외한 모든 스레드는 CPU를 포기하고 시스템이 사용 중이 아닌 경우 거의 즉시 CPU를 다시 확보합니다. 따라서 스레드 수율 및 다른 태스크가 스레드에 할당되는 것은 흔한 일이며 많은 수의 SOS_SCHEDULER_YIELD는 아무 것도 알려주지 않습니다.

 

장기 실행 쿼리는 종종 4 밀리 초 스레드 실행 한계에 도달하므로 높은 SOS_SCHEDULER_YIELD 대기 횟수 준수에 크게 기여합니다. 모든 것이 적절하게 최적화되는 한 오래 실행되는 쿼리를 갖는 데 아무런 문제가 없습니다. 일부 작업은 다른 작업보다 시간이 더 필요합니다.

 

위의 쿼리 결과에서 신호 대기 대 총 대기 비율은 매우 높지만 평균 대기 시간, 즉 총 대기 수를 대기 수로 나눈 값은 매우 낮습니다. CPU 가용성은 여기서 문제가 아니므로 비율에만 의존하는 또 다른 잠재적 위험을 보여줍니다.

 

SOS_SCHEDULER_YIELD 대기는 개별 도구의 단일 측정을 기반으로 많은 성능 조건을 진단 할 수없는 방법을 잘 보여줍니다. Windows 성능 모니터가 지속적으로 높은 CPU 활동 (80-90 %)을 보이고 있다고 상상해보십시오. 문제? 말할 수 없습니다. 해야 할 일이 많을 수 있으며 CPU는 이러한 작업을 질서 있고 효율적으로 수행하는 데 허비하고 있습니다. 그러나 많은 스레드가 준비되었지만 적시에 CPU 슬라이스를 얻지 못하는 상황에서 동일한 CPU 백분율이 표시 될 수 있습니다. 이와 같은 상황에서는 성능 모니터 (또는 원하는 경우 sys.dm_os_performance_counters)의 CPU 사용량이 높고 기본 단서가 될 sys.dm_os_wait_stats의 신호 대기 시간이 많을 수 있습니다.

 

스레드 풀

건강한 시스템에서는 THREADPOOL 대기를 거의 볼 수 없습니다. 스레드 풀에 실행 가능한 태스크에 할당 할 스레드가없는 경우 THREADPOOL 대기가 발생합니다. 구성된 최대 작업자 스레드 수가 워크로드에 작은 도구 인 경우 발생할 수 있습니다. 그러나 구성 값을 조정하기 전에 이것이 일반적인 조건인지 또는 예외적으로 매우 높은 사용량의 기간 동안 만 발생했는지 검사해야합니다. 스레드 유지 관리 비용이 발생하며 거의 발생하지 않는 조건부로 스레드 최대 값을 조정해서는 안됩니다.

 

CXPACKET

CXPACKET은 SQL Server가 병렬 실행 계획으로 쿼리에 대해 여러 스레드를 동기화하려고 할 때 발생할 수 있습니다. CXPACKET 대기에 대한 응답은 쿼리 자체에 따라 다릅니다. 쿼리가 추가 인덱스의 이점을 얻을 수 있습니까? 쿼리에 부적절한 데이터 형식과 같은 문제를 일으킬 수있는 결함이 있습니까? 쿼리가 정상으로 보이고 인덱스가 적합 해 보인다면 최대 병렬 처리 수준을 조정하여 CXPACKET 대기 시간을 줄일 수 있습니다.

 

결론

CPU 사용량과 관련된 대기 통계는 단일 문제의 직접적인 지표를 거의 제공하지 않습니다. 기껏해야 CPU 대기 통계는 문제 가능성에주의를 기울일 수 있지만, 관련된 쿼리 및 서버 작업량에 대한 추가 조사만으로 문제가 실제로 존재하는지 여부와 해당되는 경우 수행 할 조치를 결정할 수 있습니다.