[지디넷코리아]성능 저하를 분석하는 방법들

우선 느린 쿼리를 찾아내도록 한다. 단지 몇 개의 SQL 쿼리가 느린 경우에도 종종 전체 응용 프로그램이 느린 것처럼 보일 때가 있다. SQL 구문을 자동으로 생성해 주는 개발툴을 사용하는 경우에는, 이 툴에서 가능한 모든 진단 모드나 디버깅 모드를 사용하여 생성된 SQL 구문을 잡아 내도록 한다. SQL 구문을 삽입해 주는 개발도구를 사용하는 경우에는 이 작업은 훨씬 쉽다. 느린 쿼리가 무엇인 지를 파악한 후에는 다음의 과정들을 따르면 된다.

ISQL이나 SAF 등의 쿼리 툴을 사용하여 느리다고 의심이 가는 쿼리를 따로 떼어서 실행해 본 다음 실제로 느린 지를 확인한다.

SET STATISTICS IO ON과 SET SHOWPLAN ON을 사용하여 쿼리에 의해 소비되는 I/O와 선택된 Query plan을 검사한다. 논리적 페이지 I/O의 숫자에 주목한다. 논리적 I/O 숫자를 기록한다. 이는 성능 향상을 측정할 비교 수치가 된다.

만일 쿼리가 뷰나 stored procedure를 포함하고 있으면, 쿼리를 따로 추출하여 독립적으로 실행한다. 이렇게 하면 서로 다른 인덱스를 사용할 때 접근 계획을 바꿀 수 있도록 해 준다.

관계되는 테이블에 트리거가 실행되면서 자동적으로 I/O를 생성하는 트리거가 뷰가 있는 지를 확인한다.

느린 쿼리에 의해 사용되는 테이블의 인덱스들을 조사한다. 위에 나열된 방법들을 사용하여 이들이 좋은 인덱스인 지를 검사한다. 그리고 필요하면 변화를 준다.

인덱스에 변화를 준 다음 쿼리를 다시 실행하여 I/O 숫자나 접근 계획에 변화가 있는 지를 관찰한다.

성능이 향상된 것을 확인하였으면, 주 응용 프로그램을 실행하여 전체적인 성능이 향상되었는 지를 확인한다.

프로그램이 I/O나 CPU 한계치에 도달해서 동작하는 지를 관찰한다. 일반적으로 쿼리가 I/O나 CPU에 의해 성능이 제한되는 지를 조사하는 것은 많은 도움을 준다. 이러한 촛점은 진정한 병목이 일어나는 부분에 대해 노력을 집중할 수 있도록 해 준다. 예를 들어, 어떤 쿼리가 CPU에 의해 성능의 제약을 받으면, SQL 서버에 메모리를 더 설치한다고 해서 성능의 향상을 기대할 수는 없는데, 왜냐하면 메모리를 더 많이 설치할 경우에는 단지 캐시 적중률만 높여주며, 이는 이 경우 이미 충분히 높기 때문이다. SQL 서버가 I/O에 의해 제약을 받는 지, 아니면 CPU에 의해 제약을 받는 지를 분석하는 과정은 다음과 같다.

OS/2 CPU monitoring program을 사용한다. CompuServe IBMOS2 forum에서 여러 가지를 구할 수 있다.

쿼리가 수행되는 동안 CPU 그래프가 계속 높은 상태를 유지한다면(예를 들어, >70%) 이는 CPU에 의해 제약을 받는 상태이다.

쿼리가 수행되는 동안 CPU 그래프가 계속 낮은 상태를 유지한다면(예를 들어, <50%) 이는I/O 병목 상태이다.

CPU 그래프를 STATISTICS IO의 정보와 비교해 본다. @