CPU 성능 분석
SQL Server는 CPU, 메모리, 디스크가 서로 깊게 연결되어 있기 때문에 한쪽이 문제인 경우 다른쪽의 사용률도 올라가곤 해서 실제 문제가 되는 원인을 찾기가 매우 어렵다. 그래서 실제 CPU가 문제가 되는 병목현상을 알아내는것은 매우 힘든 과정이다. 또한 증설이 쉬운 메모리, 디스크와 달리 CPU는 대부분 머신이 처음 도입될때 결정지기 때문에 CPU 증설은 또다른 신규 머신을 사야지만 가능한 경우가 대부분이다.
4.1 CPU 병목 분석
SQL Server는 가능한 모든 프로세서 리소스를 최대한 사용하는 경향이 있다.
CPU 분석 카운터
Object(Instance) Counter Description Value
---------------------- -------------- --------------------------------- ---------------------------------------
Processor(_Total) % Processor Time 프로세서 사용율 % 평균 80% 이하여야 하지만 베이스라인 참고
% Privileged Time 특권 모드에서 소요된 프로세서 % 평균 10% 이하여야 하지만 베이스라인 참고
System Processor Queue Length 프로세서 작업 대기 개수 평균 2 이하여야 하지만 베이스라인 참고
Context Switches/sec 프로세서에서 쓰레드간 전환 비율 평균 5,000 이하여야 하지만 베이스라인 참고
SQL Server:SQL Statistics Batch Requests/sec 초당 들어온 SQL 배치 개수 베이스라인 참고
SQL Compilations/sec 초당 SQL 컴파일 횟수 베이스라인 참고
SQL Recompilations/sec 초당 SQL 재컴파일 횟수Disk Bytes/sec
* % Processor Time
지속적으로 80% 이상이면 병목으로 판단된다. 이때 진짜 CPU사용량이 많아서 높은건지 아니면 메모리, 디스크, 네트워크 같은 쪽의 문제때문인지 판단하는 것이 중요하다. 이 수치가 지속적으로 높고 디스크와 네트워크가 낮을 경우가 진짜 CPU 만의 문제인데 이럴 때만 프로세서쪽의 스트레스를 줄여야 하며 그렇지 않다면 해당 자원을 먼저 해결하는 것이 올바른 순서이다.
예를 들면 % Processor Time이 85%인 상태에서 I/O쪽에서 과도한 디스크 액세스가 있다면 CPU는 디스크 활동을 관리하는데 대부분의 자원을 소모하는 것일 수 있다. 이는 % Privileged Time 카운터에 반영된다. 이런 경우라면 디스크 병목을 먼저 해결하는 게 맞고 또한 디스크 병목은 메모리 병목 때문에 발생할수도 있기 때문에 진짜 범인을 찾는 것은 매우 어려운 문제이다.
이 값들은 단순한 초기 제안 값일 뿐이고 때로는 각자만의 타당한 이유로 이런 수치에 동의하지 않을 수 있기 때문에 이 수치를 권장사항이 아닌 단순한 시작점으로 생각하여 진행해야 한다.
모든 프로세서의 Total로 프로세서 시간을 추적하거나 특정 프로세서에 대한 사용률을 개별적으로 추적 할 수 있다. 만약 4개의 프로세서를 가진 컴퓨터인 경우 3개만 SQL Server에 할당되었다면 각각의 프로세서를 정확히 분리해서 데이터를 얻어야 한다. 하나의 프로세서가 최대 값을 냈지만 다른 프로세서에는 부하가 거의 없을 수 있다. 평균은 때때로 실제수치를 정확히 반영 못하기에 개략적 지표로서만 사용하고 개별 값들을 시스템의 실제 부하 및 처리 수치로 사용한다.
가상화 된 환경에서는 CPU도 가상화되어지기 때문에 표시되는 내용이 정확하지 않을 수 있다. 예를 들어 VMware 시스템에서 VMware Tools를 설치하면 VM Processor 카운터에서 호스트 컴퓨터의 프로세서 사용량을 확인할 수 있고 이 측정 값을 사용하면 OS에 표시되는 CPU 사용량이 호스팅 시스템에 반영되는지 아니면 실제로 가상 CPU를 최대로 사용하는 지 여부를 알 수 있다. 다른 말로 \Hyper-V Hypervisor Logical Processor(_Total)\% Total Run Time 이 같은 수치. 사용중인 하이퍼바이저에 따라 다른 측정 항목이 있다.
* % Privileged Time
윈도우는 유저모드와 특권모드(커널모드)라는 두 가지 모드중 하나에서 실행된다. 디스크 액세스와 같은 시스템 레벨활동은 특권모드에서 실행된다. 전용 SQL Server 머신에서 이 수치가 20에서 25 % 이상이라면 아마도 많은 외부 작업이 있을 수 있다. 원인은 I/O, 암호화 서비스 같은 필터 드라이버 또는 오래된 드라이버들 일 수 있다. 전용 SQL Server에서 이 카운터는 많아야 5에서 10%여야 하지만 더 중요한 것은 평상시의 베이스라인 데이터와 비교하는 것이다.
* Processor Queue Length
이 카운터는 프로세서 큐에 있는 쓰레드의 개수이다. 디스크쪽 카운터와 달리 Processor Queue Length는 실행 중인 쓰레드를 포함 하지 않는다. 보통의 낮은 CPU 사용률의 시스템에서는 이 카운터 수치가 0 또는 1이다.
계속해서 2보다 큰 값이라면 일반적으로 프로세스 정체를 나타낸다. 다중 프로세서이기 때문에 Processor Queue Length를 처리하는 스케줄러 수를 고려해야 할 수도 있다. 이 수치가 스케줄러의 수보다 두배 많은 경우 프로세서 병목을 표시할 수 있다. 일반적으로 1:1.
높은 % Processor Time 카운터도 바쁜 CPU상황을 알려주지만 지속적으로 높은 Processor Queue Length는 보다 명확하게 알수 있는 지표이다. 권장수치를 초과하면 이는 일반적으로 현재 프로세서 수가 서비스 할 수있는 적절간 개수보다 실행 할 스레드가 더 많음을 나타낸다.
* Context Switches/Sec
Context Switches/sec 카운터는 시스템의 모든 프로세서들이 한 쓰레드에서 다른 쓰레드로 전환하는 비율을 모니터링 한다. 컨텍스트 전환은 실행중인 스레드가 자발적으로 프로세서를 놔주거나 또는 우선 순위가 더 높은 준비 스레드가 선점할 때 또는 하위 시스템 서비스를 사용하기 위해 사용자 모드와 특권 모드 사이를 전환 할 때 발생한다. 컴퓨터의 모든 프로세서에서 실행중인 모든 스레드에 대한 Thread : Context Switches / sec의 합계이며 스위치 수로 측정됩니다. 높은 수치는 주로 CPU 속도에 의해 결정되므로 시간 경과에 따른 성능을 측정하고 이 수치를 베이스라인과 비교한다.
* Batch Request/Sec
SQL Server에 얼마나 많은 배치 쿼리들이 초당 들어오는지를 의미. 얼마나 많은 로드가 걸리는지 알 수 있는 직관적인 지표이며 이 수치가 증가할 수록 프로세서 사용률도 같이 증가하기 때문에 직접적인 관계가 있다.
하지만 빠른 쿼리와 느린 쿼리들이 항상 동시에 존재하기 때문에 이 숫자만으로는 객관적인 부하량을 알수 없으며 다른 카운터과 비교해야 한다.
SQL Server의 사양이 다 다르기 때문에 일관된 기준도 없으며 서버별로 베이스라인 수치와 비교하여 판단해야 한다.
이 때 배치와 스테이트먼트(단일 sql 문장)의 정확한 의미를 알아야 한다.
DBMS별로 같지는 않지만 SQL Server에서 스테이트먼트는 단일 sql문을 말하고 배치는 여러개의 스테이트먼트가 GO라는 구분으로 분리되어서
묶음으로 한번에 들어오는 것이다.
만일 클라이언트에서 1개의 단일 sql문과 1개의 배치문(4개의 단일 sql이 묶인) 들어오면 수치는 2이지 5가 아니다.
그래서 이름도 배치 리퀘스트인것이다.
* SQL Compilation/Sec
이 카운터는 초당 배치 컴파일과 스테이트먼트 컴파일을 함께 포함.
서버가 처음 시동될때와 페일오버 발생할 때 또는 다른 시동 이벤트때에는 특히 높을 수 있지만 그 시점을 넘으면 안정화되며 일단 한번 안정화 된 상태에서 크게 변하지 않는다. 컴파일은 시스템, 특히 프로세서에 영향을 많이 주는 무거운 작업이기 때문에 갑자기 이 수치가 증가하거나 하면 원인을 파악해야 한다.
만약 nHibernate나 엔티티 프레임워크와 같은 ORM(object-relational mappling)도구를 사용한다면 저비용 쿼리들이 대량 수행되는 패턴이기 때문에 이 카운터가 높아 질 수도 있다.
ORM 같은 경우 임시 쿼리를 매번 조합해서 보내기 때문에 다양한 패턴의 조합쿼리들이 실행된다.
그렇기에 그 패턴 쿼리별로 컴파일되기에 초당 컴파일 수치가 증가하는 것이다.
* SQL Recompilation/Sec
이 카운터는 배치와 스테이트먼트 양쪽의 재컴파일을 의미.
높은 수치는 프로세서 스트레스를 유발한다. 스테이트먼트 재컴파일이 이 카운터에 포함되기 때문에 2005이전 버전보다 더 높게 표시될 수 있다.
4.2 CPU 성능 측정 기타 도구
CPU측정을 위한 DMO역시 존재한다. 해당 시점에 측정되고 베이스라인 수치와 비교해야 한다.
* sys.dm_os_wait_stats
대기 통계는 병목현상이 시스템의 어느 부분에서 발생하는지 알수 있는 좋은 도구이다. 역시나 얼마만큼의 수치가 정상이고 얼마만큼이 비정상인지 나타내는 정해진 일괄적인 기준은 없다. 다만 시간을 들여 수치를 베이스라인 데이터를 만들고 이와 비교하여 정상인지 아닌지 판단하고 이해해야 한다.
이 DMO에 나타내는 특정 항목은 CPU 항목의 병목인지 바로 알 수 있기도 한다. 대표적으로 CXPACKET 대기이다. 그러나 SQL Server 2016 sp2와 SQL Server 2017 CU3에서는 약간의 변화가 생겼는데 여전히 중요한 CXPACKET 대기와 그닥 중요하지 않은 CXCONSUMER로 분리되었다. Azure SQL Database에서도 분리된 항목으로 표시된다.
이번 분리의 핵심의미는 CPU 병렬수행작업때 소비자와 생산자를 비교해서 판단하는 것이다. 병렬수행 작업에서 데이터를 받는 간단 작업만 하는 소비자(CXCONSUMER)는 일반적으로 무시해도 될 정도이며, 반대로 큰 부하를 생산하거나 데이터를 밀어넣는 작업을 하는 CXPACKET은 여전히 중요한 평가 기준이다. 그러므로 CXPACKET 대기를 실제 CPU에 로드를 주는 것으로 생각해야 한다.
* sys.dm_os_workers 와 sys.dm_os_schedulers
이 DMV들은 Windows OS의 워커와 스케줄러 쓰레드를 나타낸다. 이 DMO를 실행해서 실행 가능한 상태에있는 프로세스 수를 확인할 수 있습니다.
4.3 프로세서 병목 해결 방법
* 어플리케이션 워크로드 최적화
* 과도한 컴파일/재컴파일 제거
* 더 빠른 프로세서 사용
* 불필요한 소프트웨어 제거
* 어플리케이션 워크로드 최적화
프로세서 집약적인 쿼리를 식별하기 위해선 확장 이벤트 세션을 이용하여 모든 SQL을 캡처하고 CPU컬럼 기준으로 결과를 그룹화 해야 한다. CPU 스트레스 원인의 대부분은 과도한 계산작업 때문이 아니고 논리적 I/O 경합쪽에서 발생한다.
다음 DMV에서 실제 쿼리 통계를 볼수 있다.
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats
query hash와 query plan hash를 사용하여 쿼리를 식별한 후 튜닝해야 한다.
* 과도한 컴파일/재컴파일 제거
특별히 이전에 언급했듯 ORM 도구로 작업 할 때는 특정 수의 쿼리 컴파일 및 재 컴파일이 예상된다. 문제가 되는 경우는 지속적으로 높은 수치를 계속 유지하고 있을 때이다. 다수의 컴파일과 적은수의 재컴파일이 있는 경우 적은 수의 쿼리만 재사용 되고 있다는 것을 의미한다. 다수의 재컴파일은 높은 프로세서 사용을 유발한다.
* 더 빠른 프로세서 사용
CPU문제의 가장 쉬운 방법은 프로세서 파워를 올리는 것이다. 비록 높은 비용이 들기는 하지만 가장 빨리 효과를 본다.
높은 % Processor Time과 낮은 Processor Queue Length : 개별 프로세서 성능 높이기
높은 % Processor Time과 높은 Processor Queue Length : 프로세서 수 늘리기
* 불필요한 소프트웨어 제거
SQL Server와 동일한 서버에서 불필요한 소프트웨어를 실행해서는 안됩니다.