SQL Server CPU Performance Problem 01

원문 : http://technet.microsoft.com/en-us/magazine/cc137784.aspx

- CPU 하이퍼 쓰래딩은 일반적으로 Disable 하는 것이 좋다.

- CPU 가 DB의 Page을 읽을 때 Access 속도는 Physical Memory (RAM) 의 경우 약 0.5ms 가 Disk 일 경우 2~4ms (최적의 경우) 에서 4~10ms가 소요 된다. 결과적으로 Physical Memory의 데이터를 읽는 것이 성능상 가장 큰 이점이 있다.
설명 : Physical Memory에서 page를 read 하는 것을 soft page fault 라 하고 Disk에서 page를 읽는 것을 hard page fault 라 한다.

하나! 작업 부하 증가

%Processor Time 이 80%가 지속적으로 상회하고 있으면 CPU에 성능 로드가 있다고 봐도 무방하다. 이와 동시에 System\Processor Queue Length 를 확인해야 한다. SQL Server 는 하나의 논리 CPU에 하나의 Thread를 할당한다. 일반적으로 5배가 넘을 경우 성능에 문제가 있는 것으로 볼 수 있으며, 2배가 되더라도 성능에 문제가 있을 수 있다.

다만 해당 서버가 DBMS 외 다른 Role을 추가로 가지고 있다면 Processor Time 및 Context Switches/sec를 동시에 검토하여 서버 분산 여부를 판별 할 수 있다. 즉 Processor Time이 높고 동시에 Context Switches가 많이 발생 하게 되면 Role들 사이에 CPU 자원에 대한 경합이 일어나는 것으로 볼 수 있다. 이러한 현상이 지속적으로 발생하고 있다면 Role 분산을 통해 문제 현상을 해결 할 수 있다. 특히 Exchange 서버 Role이 동시에 운영되게 된다면 각 Role은 Physical Memory를 쓰고자 하고 이로 인해 극심한 Context Switching 동시에 Memory의 Hard Page Fault가 발생하게 된다. Context Switching운 논리 CPU 당 5000번 이상 발생 할 경우 일경우 성능에 문제를 발생 시키는 것으로 볼 수 있다.

높은 CPU 사용률과 함께 대기중인 큐가 많은 경우 SQL Server:SQL Statistics 성능 개체에 있는 Compilations/sec와 Re-Compilations/sec 카운터를 검토하는 것이 좋다. 쿼리 계획 컴파일 및 재컴파일은 CPU 사용률을 높힌다. Re-Compilations 값은 0에 가까워야 하지만, 시스템의 추세를 주의 깊게 검토하여 서버의 일반적인 동작과 정상적인 컴파일 수를 판단하는 것이 좋다. SQL Server:SQL Statistics 성능 개체에 있는 Batch Requests/sec를 검토하여 실제로 요청되는 쿼리와 해당 쿼리가 재컴파일 되는 빈도를 비교할 수 있다.

둘 ! 메모리 부족

SQLServer:Buffer Manager\Buffer Cache Hit Ratio 가 97% 미만 인 경우 메모리가 부족을 의심 할 수 있다.
또한 PLE (SQLServer:Buffer Manager\Page Life Expectancy)가 300초 미만 일 경우 해당 데이터 페이지는 자주 플러쉬 되고 있는 것으로 볼 수 있다. 다만 Checkpoint가 발생한 경우에는 버퍼 케쉬를 정상적으로 디스크에 플러쉬 함에 따라 일시적으로 PLE 값이 감소 할 수 있다.

Physical Memory (RAM)이 부족할 경우 버퍼 캐시 및 프로시저 캐시를 디스크로 플러시 해서 부족한 메모리를 확보해야 한다. 리소스 모니터는 플러쉬할 페이지를 결정하는 SQL Server 프로세스로써, RAM 부족할 경우 빈번하게 발생하게 된다. SQL Server: Buffer Manager\Lazy Writes/sec 카운터를 통해 확인 할 수 있다.

셋! 쿼리 성능

SQL Server로 새 쿼리가 제출될 때 쿼리 계획이 평가, 최적화 및 컴파일되어 프로시저 캐시에 저장된다. 서버로 쿼리가 제출될 때마다 요청과 일치하는 쿼리 계획이 있는지 확인하기 위해 프로시저 캐시가 검토된다. 일치하는 쿼리 계획이 없으면 SQL Server에서 새 쿼리 계획을 만들게 되는데 이 경우 비용이 들 수 있다.
SQLServer:Plan Cache\Cache Hit Ratio: SQL Plans 가 70% 미만인 경우 쿼리에 문제 있는 것으로 대략적인 판별은 가능하다.