Profiler Trace(프로파일러 추적)을 테이블로 저장하여 분석하는 방법

성능 튜닝을 위해 Profiler Trace 수집은 필수적인 작업입니다. Profiler Trace 수집은 Profiler 툴을 이용하여 손쉽게 수행할 수 있으며 수집된 Profiler Trace는 Profiler 툴 자체의 필터링 및 그룹핑 기능을 이용하여 분석할 수 있습니다. 하지만, Profiler Trace의 크기가 크거나 여러 개의 롤오버(Rollover) 파일로 나뉘어 저장되었을 경우에는 Profiler 툴을 이용해 분석하는 데 시간이 많이 소요되는 불편함이 있으며 기본적으로 제공되는 필터링 및 그룹핑 기능만으로는 분석하는 데 부족함을 느끼기도 합니다.

 

Profiler Trace를 SQL Server의 테이블 형태로 저장하게 되면 쿼리문을 이용하여 보다 유연하게 분석 작업을 진행할 수 있습니다. Profiler Trace를 SQL Server의 테이블로 저장하는 방법은 Profiler Trace 수집시 수집된 내용들이 직접 테이블로 저장되도록 설정하는 방법, 파일(.trc) 형태로 이미 수집된 Profiler Trace를 Profiler 툴로 열었다가 '다른 이름으로 저장'을 통해 테이블로 저장하는 방법, 파일 형태로 이미 수집된 Profiler Trace를 시스템 함수 'fn_trace_gettable'을 호출하는 쿼리문을 통해 테이블로 저장하는 방법 등이 있습니다. 일반적으로 Profiler Trace는 일단 파일(.trc) 형태로 수집하고 필요시 쿼리문을 통해 테이블로 저장하는 방법이 가장 유용하므로 쿼리문을 통해 Profiler Trace를 테이블로 저장하고 이를 분석하는 방법에 대해서 살펴 보겠습니다.

 

fn_trace_gettable 시스템 함수는 Profiler Trace 파일을 읽어서 테이블 형태의 결과로 반환하는 테이블 반환 함수(Table Valued Function)로 다음과 같은 구문을 갖습니다. 

fn_trace_gettable(filename, number_files)
파라미터 정보:
     filename - 읽을 첫 Trace 파일(.trc)의 절대 경로를 지정

     number_files - 읽을 롤오버 파일의 수를 지정함. default를 지정시 Trace의 끝에 도달할 때까지 모든

                         롤오버 파일을 읽음. 

 

fn_trace_gettable 시스템 함수를 사용하여 다음의 예와 같은 쿼리문을 실행함으로써 Profiler Trace 파일을 테이블로 저장할 수 있습니다. 

select * into trace from fn_trace_gettable('c:\trace\data.trc', default)

 

Profiler Trace가 테이블로 저장되면 쿼리문을 통해 튜닝이 필요한 쿼리들을 확인해 볼 수 있습니다. 일반적으로 성능 튜닝을 위해 Profiler Trace를 분석할 때에는 이벤트 ID 10에 해당하는 RPC:Completed와 이벤트 ID 12에 해당하는 SQL:BatchCompleted 이벤트만을 검토하면 되므로 쿼리문 실행시 다음의 예와 같이 eventclass 칼럼에 대해 필터링을 합니다. 다음의 예는 쿼리의 처음 40바이트 기준으로 그룹핑하여 평균 CPU 사용량이 높은 쿼리 유형을 순서대로 보여주는 쿼리문으로 CPU 사용률이 높은 환경에서 튜닝이 필요한 쿼리를 찾는 용도로 사용될 수 있습니다. 

select substring(textdata, 1,40) as [Query], avg(cpu) as [Avg. CPU], avg(duration) as [Avg. Duration], avg(reads) as [Avg. Reads], count(*) as [Execution Counts]
from trace
where (eventclass = 10 or eventclass = 12)
group by substring(textdata, 1, 40)
order by [Avg. CPU] desc

 

상기의 쿼리문 실행시 다음과 같은 형태의 결과를 확인할 수 있는데 실행 횟수(Execution Counts)가 크면서 평균 CPU 사용량(Avg. CPU)이 큰 쿼리 유형에 대해서 우선적으로 튜닝을 검토해 볼 수 있습니다. 

 

SQL Server 2008 온라인 설명서의 sp_trace_setevent(Transact-SQL) 항목(http://msdn.microsoft.com/ko-kr/library/ms186265.aspx)을 통해 SQL Server 2008에서 Profiler Trace를 통해 수집 가능한 모든 이벤트의 ID, 이름, 설명을 확인할 수 있으며 필요에 맞는 적절한 이벤트의 ID를 확인하여 분석을 위한 쿼리문에 활용할 수 있습니다.