[MS-SQL] 쿼리 조회 시간이 오래 걸리는 쿼리에 대한 성능 분석하는 방법 (SET STATISTICS IO, SET STATISTICS TIME )

쿼리에 대한 성능 분석하는 방법

쿼리(Query) 실행시간이 오래 걸리는 쿼리가 있을 때 실행시간을 줄이기 위해(오래걸리는 문제를 해결) 쿼리의 성능을 분석해야하는 상황이 발생한다.

프로파일러 및 실행 계획을 사용하는 것도 방법이지만 SET STATISTICS IO 및 SET STATISTICS TIME 사용하여 확인하는 방법에 대해 알아보자.

-- 컴파일 시간 및 실행 시간
SET STATISTICS TIME ON;

-- 각 구문의 IO 실행 결과
SET STATISTICS IO ON;

STATISTICS IO 옵션을 ON으로 설정하면 통계 정보가 표시된다.

SET STATISTICS TIME 옵션을 ON으로 설정하면 문에 대한 시간 통계가 표시된다.

SET STATISTICS TIME ON 문은 쿼리의 실행 시간 통계에 대한 매우 짧지만 매우 유용한 보고서를 제공한다.

쿼리 질의하기 전에 앞에 위의 구문을 넣어 함께 실행하면 실행시간 및 각 구문의 IO 실행결과를 알 수 있다.

[성능 테스트 확인 방법]

실행시간을 확인하기 위해 실행할 쿼리문 앞에 위 2개의 STATISTATICS TIME 과 IO 옵션을 ON으로 실행하는 명령어를 실행 후 조회데이터가 보이는 뷰에서 메시지 탭을 클릭하면 확인이 가능하다.

SET STATISTICS TIME ON;
SET STATISTICS IO ON; --시작

SELECT * FROM TB_CUST_SAMPLE

SET STATISTICS IO OFF;  --종료

다음 결과는 위 SELECT 문의 결과는 아니며 여러테이블의 데이터를 조인해서 가져오는 통계 쿼리의 결과이다.

다음은 SET STATISTICS IO가 켜진 출력의 예이다.

테이블	테이블 이름
검색 수	테이블 에서 스캔 또는 탐색이 시작된 횟수
논리적 읽기 수	메모리 내 캐시(버퍼 풀)에서 읽은 데이터 수
물리적 읽기 수	(디스크에서 읽은 페이지 수) 디스크로부터 버퍼풀에 전송된 페이지 수(데이터 전송이 완료 될 때까지 블록 또는 대기가 발생)
미리 읽기 수	비동기적으로 디스크로부터 데이터를 읽어 버퍼풀에 넣는 페이지 수(쿼리에 대해 캐시에 넣어진 페이지 수)
LOB 논리적 읽기 수	LOB 데이터를 읽은 페이지 수 (데이터 캐시에서 읽은 text, ntext, image 또는 큰 값 유형(varchar(max), nvarchar(max), varbinary(max))의 페이지 수)
LOB 물리적 읽기 수	디스크에서 읽은 text, ntext, image 또는 큰 값 유형의 페이지 수
LOB 미리 읽기 수	LOB 데이터를 읽은 페이지 수( 쿼리에 대해 캐시에 넣어진 text, ntext, image 또는 큰 값 유형의 페이지 수)

논리적 읽기 수가 많으면 쿼리의 성능이 낮아져 실행 속도가 느려진다.
논리적 읽기 수가 많으면 긴 실행 시간과 높은 CPU 소비가 발생하는데

쿼리 튜닝이 필요한 시간이 오래 걸리는 쿼리를 위의 구문과 함께 실행하여 논리적 읽기 수가 높은 테이블을 중점적으로 튜닝하면 쿼리 성능 개선에 도움을 줄 수 있다.

[마이크로소프트 기술 문서 자료]

[REFERENCE]