자동화된 추적을 설정하기 위한 9단계

최소한의 노력으로 성능정보 획득

Steven Berringer

프로필러는 SQL 서버에서 제공하는 추적도구로 SQL 서버 기반 애플리케이션의 성능을 튜닝하고 디버깅하기 위해 사용되는 매우 유용한 도구이다. 데이터베이스 관련 애플리케이션에서 SQL 서버로 보낸 요청을 추적할 수 있고 해당 데이터를 사용하여 성능을 향상시키기 위해 사용할 수 있다. 예를 들어 프로필러를 사용하여 오랜 시간동안 진행중인 프로세스를 추적하게 되면 실제로 SQL 서버가 테이블 스캔을 사용하는지, 테이블 잠금을 설정하고 있는지, 다른 성능의 문제를 발생시키는 이벤트를 발생시키는지 등을 점검할 수 있다. 필자의 경우 개발자로부터 데이터베이스 코드에 문제가 있어서 디버깅 요청을 받게 되면 프로필러를 사용한다. 가끔씩 시간적인 여유가 허락되면 먼저 선수를 쳐서 자주 사용하는 지속시간 관련 추적을 설정한 다음 개발자에게 그 결과를 통보하기도 한다. 지속시간 추적은 1초 이상 실행되는 프로세스를 찾아내는 역할을 한다. 1초라는 시간은 매우 적절한 임계값으로 추적의 대상이 그렇게 많지 않은 상태에서 자원을 소모하는 프로세스를 좀 더 쉽게 찾아낼 수 있도록 해 준다.

시간적인 여유가 있을 때에만 추적을 실행시켰기 때문에 추적결과가 산발적이고 일관성없게 수집되었다. 추적 작업을 정기적인 일정에 따라 실행시킬 수 있다면 하루 동안 시간에 따라 어떠한 성능상의 문제가 발생했는지에 대한 성능 데이터를 효과적으로 수집할 수 있었을 것이다. 결국 솔루션을 찾기 위해 노력한 결과 하루 동안의 각 시간대별로 가장 오랫동안 실행된 프로세스가 무엇인지를 찾아낼 수 있었고 데이터베이스에 접근한 프로세스 중에서 가장 느리게 실행된 프로세스가 무엇인지를 알아낼 수 있었다. 찾아낸 솔루션은 SQL 서버의 예약된 작업을 통해 지속시간 추적을 주기적으로 실행하는 방법이다. SQL 서버에서 제공하는 두 가지 도구, 작업 스케줄러와 프로필러의 추적 기능을 사용하여 성능관련 추적 데이터를 수집하는 작업을 설정할 수 있었으며, 해당 추적 데이터를 테이블로 업로드한 다음에 그 내용을 분석할 수 있었다. 작업 스케줄러와 프로필러 추적을 함께 사용하게 되면 별도의 사용자의 개입없이 사전에 설정된 일정에 따라 제한된 시간만큼만 주기적으로 추적을 실행하도록 설정할 수 있다.

이번 호의 기사에서는 이미 프로필러에 대한 사전 지식이 있고 이미 추적을 실행시키고 만들어 본 경험이 있다고 가정하고 논의를 진행한다. 만약 아직 프로필러 인터페이스를 통해 사용자정의 추적을 생성해 본 경험이 없다면 SQL 서버에서 기본적으로 제공하는 추적 템플릿을 실행하여 프로필러 추적이 어떤 식으로 동작하는지에 대해서 사전에 공부해둘 필요가 있다. 프로필러 관련 팁에 대해서는 http://www.sqlmag.com 에서 Profiler 키워드 검색을 해보면 된다. 이번 호의 기사에서는 프로필러를 사용하여 추적을 만든 다음, SQL 서버 명령줄 도구인 쿼리 분석기와 같은 도구를 사용하여 해당 추적을 실행할 수 있도록 하는 예제를 소개하고자 한다. 너무 많은 이벤트를 추적할 수 있도록 설정하게 되면 추적 작업으로 인해 서버의 성능에 영향을 미칠 수는 있지만 추적 작업은 단순하게 실행중인 프로세스에 대한 추적이 때문에 데이터나 트랜잭션, 또는 다른 프로세스에 변경이나 악영향을 미치지는 않는다.

설정된 시간 간격을 기준으로 SQL 서버에서 현재 어떠한 작업이 진행되고 있는지에 대한 데이터를 수집하는 프로필러 추적을 생성하는 9 단계에 대해서 소개한다. 시작하기 전에 예제를 위한 테이블과 저장 프로시저를 저장할 데이터베이스를 별도로 생성할 것을 권한다. 예약된 작업을 생성하는 예제에서 필자의 경우에는 tracerDB를 호출한다. 다른 데이터베이스를 사용해야 한다면 예제 스크립트를 적절하게 수정하면 된다. 두 개의 예약된 작업을 설정하는 T-SQL 스크립트가 [웹 리스트 1](www.adminmag.com의 자료실에서 다운로드가 가능하다)에 나타나 있다.

1 단계: 프로필러를 사용하여 추적 정의를 생성

프로필러를 사용하여 추적에 대한 정의를 생성하기 위해서 먼저 기본으로 제공되는 추적 템플릿을 활용할 것을 권고한다. 프로필러의 파일 메뉴의 열기 옵션을 선택하고, 추적 템플릿, SQLProfilerTSQL_Duration을 선택한 다음, 다른 이름으로 저장하기 옵션을 사용하여 수정한 템플릿을 새로운 템플릿 파일로 저장한다.

그 다음에 필요한 데이터 컬럼을 추가하고, 추적을 위한 필터를 조정하면 된다. 추적 템플릿에서 SQL 서버가 백그라운드로 실행하는 프로세스에 대한 추적은 제외하고자 한 다면 NTUserName 필터를 추가하고 SQL 서버와 SQL 서버 에이전트 서비스를 실행하는 데 사용하는 사용자계정에 대해서 Not Like 조건식을 추가하면 된다. 실제 운영환경에서 서비스 계정으로 실행되는 예약된 작업이 있는 경우, 작업 스케줄러에 의해서 실행되는 프로세스는 추적대상에서 제외하고자 하는 상황이 실제 사례가 될 수 있다. 각 프로세스가 실행된 시간 정보를 추적하기 위해서는 StartTime과 EndTime 데이터 컬럼을 추가한다. 실제로 실행된 프로세스에 대해서 제대로 된 추적을 하기 위해서는 DatabaseID와, Reads, Writes 등의 데이터 컬럼도 추가할 필요가 있다.( DatabaseID는 항상 불변하는 값을 가지기 때문에 추적을 위해서는 가변적인 DatabaseName보다는 DatabaseID를 사용하는 것이 바람직하다.)

실제적으로 주의를 기울여야 하는 프로세스만 추적하기 위해서 Duration 필터는 적어도 1000 밀리초(1초) 이상으로 설정하는 것이 좋다. 각자의 환경에 따라 실제로 분석의 대상이 되는 프로세스의 양을 적절하게 유지하기 위해 Duration 필터를 탄력적으로 유지하면 된다. 실제로 SQL 서버는 1초 미만의 시간에도 엄청나게 많은 작업을 수행할 수 있다. 그렇기 때문에 1초면 SQL 서버가 수행하는 대부분의 프로세스가 종료될 수 있다. 저장을 클릭하면 현재의 추적 템플릿의 변경내역을 저장할 수 있다.

2 단계: 추적 실행

추적 템플릿의 변경사항을 저장한 다음, 추적 산출물 파일을 생성하기 위해 해당 추적 템플릿을 실행한다. 나중에 추적 산출물 파일을 계속 사용하기 위해서 추적결과를 SQL 서버의 테이블로 저장할 수도 있다. 파일 메뉴의 새로 만들기, 추적을 선택한 다음, 앞 단계에서 생성한 추적 템플릿을 선택한다. 데이터베이스 연결 대화상자에서 연결하기 원하는 서버를 선택한 다음, 템플릿 이름 드롭다운 박스에서 앞 단계에서 만들어 놓은 템플릿을 선택하고 실행을 클릭한다. 추적창에 적절한 양의 추적결과가 수집될 수 있도록 몇 분간 실행시킨 다음에 추적을 중지시킨다.

3 단계: 추적 정의 및 추적 산출물을 파일로 저장

추적결과를 T-SQL 스크립트로 저장해 보자. 파일 메뉴에서 추적 스크립팅을 선택하고 SQL 서버 2000 옵션을 선택한다. 다른 이름으로 저장 대화상자가 나타나면 .sql 확장자로 추적을 스크립트화하여 T-SQL로 저장할 수 있다. 추적을 스크립팅한 결과가 저장 프로시저로 생성된다. 다음 단계는 추적결과를 포함하고 있는 파일을 만들면 된다. 파일 메뉴의 다른 이름으로 저장, 추적 파일 옵션을 선택하여 해당 파일을 .trc 확장자로 저장한다. 지금 만든 추적결과 파일은 앞으로 SQL 서버의 테이블로 만들기 위해서 사용된다. 이번 호의 예제에서는 추적 파일의 명칭을 D:\sqlbackup\trace_dump_00.trc 로 지정하였다.

4 단계: 추적 정의 스크립트를 생성하기 위한 저장 프로시저 생성

그 다음에 3 단계에서 생성한 .sql 스크립트 파일을 쿼리 분석기로 로드한다. 3 단계에서 생성한 파일은 일정화된 추적을 실행시키기 위한 가장 핵심부분이다. 필자의 경우에는 예약된 작업에 많은 양의 SQL 문장을 실행하는 것을 별로 선호하지 않고, 가능하면 모든 작업을 저장 프로시저내에서 처리하여 각 진행단계에 대해서 좀 더 쉽게 파악하고 관리할 수 있도록 하는 방법을 선호한다. 그러므로 가능하면, 3 단계에서 생성한 스크립트를 저장 프로시저로 변환할 것을 권고한다. 파일명과 추적ID 를 매개변수로 갖는 저장프로시저를 생성하면 된다. 출력 매개변수로 설정된 추적 ID는 실제로 예약된 추적작업만을 설정하기 위해서는 필요하지 않지만 저장프로시저로 독립적으로 각각 추적을 실행시키기 위해서는 추적 ID를 알아야만 한다.

3단계에서 생성한 스크립트를 저장프로시저로 변경하려고 하는 경우, 스크립트상에 두 가지 중요한 부분을 수정해 주어야 한다. 먼저, "InsertFileNameHere" 문자열을 sp_trace_create 저장프로시저에 매개변수로 전달되어야 하는 파일명칭으로 변경한다. 그 다음에 RAISERROR 문장을 추가하여 sp_trace_create 저장 프로시저를 수행하는 과정에서 에러가 발생한 경우, 해당 에러메시지를 반환할 수 있도록 변경한다. Sp_trace_create 저장프로시저에 전달되는 파일명칭 매개변수에는 확장자가 없는 상태로 전달되어야 한다. 만약 확장자가 붙어있는 상태로 파일명칭을 입력하면 에러가 발생하게 된다. 반면에 fn_trace_gettable() 함수에서는 .trc 확장자를 추가한 상태로 파일명을 전달해야 한다.

5단계: 추적 출력 파일로부터 테이블 생성

다음 단계는 SQL 서버 데이터베이스내에 예약된 추적의 결과를 저장할 테이블을 생성하는 것이다. SQL 서버에서 제공하는 기본 함수인 fn_trace_gettable() 함수를 사용하면 추적 산출물 파일의 데이터를 SQL Server 데이터베이스의 테이블로 업로드할 수 있다. 별도의 CREATE table 문장을 입력할 필요없이 다음과 같이 적절한 추적 산출물 파일 명칭만 지정해 주면 추적 산출물 파일을 테이블로 만들 수 있다.

SELECT *
INTO mytrace
FROM 
  ::fn_trace_gettable('D:\sqlbackup\trace_dump_00.trc', default)
  /* 두번째 매개변수를 default로 설정하면, 추적 작업이 하나 이상의 파일을 생성할 때 전체 파일에 대해서 업로드 작업이 수행되도록 할 수 있다. */
  

이 때 사용되는 추적 산출물 파일은 사전에 추적을 실행한 결과를 파일형식으로 저장해 둔 것이다. 어떤 종류의 추적 템플릿을 기반으로 추적 산출물 파일이 생성되었는지와 상관없이 추적 결과에 포함된 모든 컬럼(시작시간, 종료시간, 읽기 수, 지속시간 등)이 테이블의 컬럼으로 만들어지게 된다.

6 단계: 추적 경로 및 파일 명칭을 저장하기 위한 테이블 생성

다음 단계는 예약된 작업을 생성하는 과정에서 발생하게 된다. 예약된 작업을 설정하는 과정에서 필요한 추적 파일의 경로와 명칭을 수작업으로 입력하지 않기 위해서 추적 파일의 경로와 파일 명칭을 별도의 테이블로 저장할 수 있다. 이 테이블은 SQL 서버 추적을 테이블에 기록하는 경우에 쉽게 변경이 가능하며 다수의 추적 및 예약된 작업을 관리할 수 있게 해 준다. 추적 경로 및 명칭을 저장하기 위한 테이블을 생성하는 과정은 다음과 같은 코드로 매우 간단하게 처리할 수 있다.

CREATE table tracer(
  tid int primary key 
     identity(1,1) NOT NULL,
  output_path_file varchar(100) NOT NULL,
  output_path_file_ext AS 
     (output_path_file + '.trc'))

위의 테이블을 생성한 다음, 전체 추적 경로와 .trc 확장자를 제외한 파일명을 행 정보로 추가한다. 필자의 INSERT 문장은 다음과 같다.

INSERT tracer (output_path_file)
VALUES 
  ('\\networkShare\folder\mytraceOutput')

mytraceOutput 는 지속시간 추적결과가 저장되어 있는 추적 산출물 파일의 명칭이다. 확장자는 제거되었고 확장자를 포함한 전체 파일명칭이 필요하다면 추적 테이블에 계산된 컬럼을 추가하여 확장자가 포함된 명칭을 추가할 수도 있다. 특히 추적작업이 운영환경의 서버에서 실행되는 경우라면 추적 파일은 네트워크 드라이브나 SQL 서버가 사용하지 않는 드라이브에 위치시킬 것을 권고한다. 각자의 환경에 맞추어 가장 이상적인 배치를 어떻게 할 것인지를 체크해 보아야 한다. 일부 추적의 목적은 성능 튜닝을 위해 수행되는 경우가 있으므로 어떻게 추적 관련한 구성을 하느냐에 따라 추적의 결과가 달라질 수도 있다. SQL 서버를 실행할 때 사용하는 SQL 서버 서비스 계정은 반드시 추적 산출물 파일에 언제나 쓰기 권한을 가질 수 있도록 설정해야 한다. 예약된 작업도 추적 산출물 파일에 대한 전체 권한이 설정된 계정으로 실행되어야만 한다.

7단계: 추적을 실행하기 위한 작업일정 설정

추적을 실행하고, 중지하는 역할을 하는 두 가지 예약된 작업을 생성한다. [웹 리스트1]에는 두 가지 예약된 작업을 생성하는 스크립트가 나타나 있다. 첫번째 작업은 하나의 단계로 구성되고, 앞에서 생성한 _duration_trace 저장 프로시저를 실행하여 추적을 실행하는 역할을 한다. 저장 프로시저를 실행시키기 전에 예약된 작업에서는 추적(tracer) 테이블을 조회하여 추적작업이 생성하게 될 추적산출물 파일의 경로 및 명칭을 찾아오게 된다.

- 작업 1, 단계 1
DECLARE @path_file nvarchar(200)
SELECT 
  @path_file=output_path_file 
  FROM tracer WHERE tid = 1
EXEC _duration_trace @path_file, null

WHERE tid = 1 절은 원하는 추척 산출물 파일을 포함하고 있는 추적 테이블의 행 내용을 반환한다.

작업에 대한 예약일정을 설정하는 작업은 추적을 실행시킬 빈도에 따라서 결정된다. 첫 번째 작업단계는 추적을 시작하는 작업이고, 두 번째 작업단계는 추적을 중단하는 작업이다. 만약 두 번째 작업단계를 누락시키게 되면 추적은 무제한적으로 계속 실행되게 되기 때문에 반드시 두 번째 작업단계에 대한 예약일정이 추가되었는지 확인해야만 한다. 예약된 작업에서 기본적으로 제공하는 알림 기능을 사용하여 작업이 실패한 경우, 호출기나 Email로 결과가 통보될 수 있도록 설정하는 것이 필요하다. 또한 sp_trace_create 프로시저에 최대 파일 크기를 설정하여 디스크 공간이 과도하게 소모되지 않도록 예약된 작업을 통제할 필요가 있다. 첫 번째 작업단계는 추적을 실행시키고 곧 바로 종료되게 된다. 추적이 실행되는 동안 작업이 지속되지 않는다. 추적이 실행 중인지 여부를 체크하기 위해서는 쿼리 분석기에서 다음과 같은 SQL 문장을 실행하면 된다.

SELECT * FROM 
  ::fn_trace_getinfo(default)
  • Default 또는 0으로 설정되면, 현재 실행중인 추적 전체가 반환된다.

위의 문장은 SQL 서버에서 현재 실행중인 각 추적건에 대해서 여러 개의 행으로 표시해 준다. 각 추적은 각각 별도의traceId 컬럼값에 식별자를 갖는다. traceId는 sp_trace_create 저장 프로시저가 실행된 결과로 반환되는 추적ID 식별자이다. 현재 실행중인 추적이 없으면, 위의 문장은 결과를 하나도 반환하지 않는다.

8단계: 추적을 중단하고, 파일을 업로드하고, 해당 파일을 삭제하는 작업일정 설정

두 번째 예약된 작업은 세 가지 기능을 수행하게 되며, 실제로 대부분의 작업을 두 번째 예약된 작업에서 수행한다고 할 수 있다. 먼저 추적을 중단하고, 해당 추적결과로부터 추적 산출물 파일에 저장하고, 다시 이를 mytrace 테이블에 해당 결과를 업로드하고, 하드 디스크 상에 존재하는 추적 파일을 삭제한다.

[리스트 2]의 스크립트는 위의 세가지 작업단계를 생성하는 역할을 한다. 첫 번째 단계에서는 sp_trace_setstatus 저장 프로시저를 두 번 호출하는데, 첫 번째는 추적을 중단시키기 위해서 두 번째는 추적 파일을 닫기 위해서 호출한다. 이러한 작업이 완료될 때까지 추적은 계속 실행되게 되며 추적 산출물 파일은 디스크상에서 계속 사용 중이기 때문에 이동시킬 수도 없고 삭제할 수도 없다. 이 단계의 앞부분에서는 매개변수 테이블(tracer)을 조회한 다음, 그 결과를 추적 산출물 파일의 명칭과 전체 경로를 좀 더 쉽게 설정하기 위해서 사용한다. 매개변수 테이블에서 적절한 정보를 획득하게 되면 모든 작업단계에 파일 명칭을 하드코딩하지 않아도 된다.

작업의 두 번째 단계는 매개변수 테이블(tracer)의 output_path_file_ext 계산된 컬럼에 .trc 확장자가 포함되어 있는 파일명칭을 조회하여 변수에 넣는 작업이다. @path\_file\_ext 변수에 파일 명칭이 저장되었다면 다음의 SQL 문장을 실행하여 지정된 추적 산출물 파일의 추적결과를 사전에 만들어 놓은 테이블로 업로드 할 수 있다.

INSERT mytrace
SELECT *
FROM 
  ::fn_trace_gettable(@path_file_ext, default)

세 번째 단계는 매우 중요하다. Master.dbo.xp_cmdshell 확장 저장프로시저를 사용하여 추적 산출물 파일을 삭제해 주어야만 한다. Sp_create_trace 저장 프로시저 실행될 때, 지정된 추적 산출물 파일이 디스크상에 이미 존재하는 경우 "Error 12"가 발생하며 다음 예약된 일정작업은 실패하게 된다. 다음 단계로는 추적을 중단시키는 단계로 추적을 얼마나 오랫동안 실행시킬 것을 결정하게 된다. 만약 최초 작업이 오전 10:10에 실행하여 5분간 추적데이터를 수집하려고 하는 경우 두 번째 작업에 대한 작업일정을 오전 10:15로 지정하면 된다.

9 단계: 추적 데이터에 대한 조사 및 활용

이제 최종적으로 작업의 결과를 확인하기만 하면 된다. 예약된 추적작업은 SQL 서버에서 일정에 따라 요청을 할 때마다 mytrace 테이블에 지속적으로 저장되기 때문에 쿼리를 사용하여 그 결과를 조회하거나 분석할 수 있다. 이번 호의 예제로 사용하고 있는 지속시간 추적 템플릿의 경우 성능향상 목적으로 활용하기 위해 추적 데이터를 수집한 것이다. mytrace 테이블에 쿼리를 함으로써 쉽고 빠르게 현재 데이터베이스에서 오랜 시간 동안 실행되는 프로세스가 무엇인지 찾아낼 수 있었다. 또한 가장 큰 장점은 성능저하나 추가적인 관심을 기울여야 하는 데이터베이스를 찾아내고, 시간대별 추이를 분석할 수 있다는 것이다. 각 프로세스에 대한 시작 시간을 추적했기 때문에 가장 느리게 실행되는 프로세스가 무엇인지를 찾아낼 수 있었다. 또한 어떤 쿼리가 가장 읽기수와 쓰기수가 많은지 체크할 수 있고, 어떤 어플리케이션에서 언제 실행시킨 쿼리가 가장 빈번하게 사용되는지도 판단할 수 있다. 결국 이러한 추적결과 정보를 근간으로 쿼리를 최적화하고 적절한 인덱스를 생성하고 저장 프로시저를 수정한 결과 1초 이내에 수행될 수 있도록 조치할 수 있었다.

고려사항

예약된 추적 작업을 설정하기 위해서는 몇 가지 고려할 사항이 있다. 추적을 시간 간격에 따라 실행해야 하기 때문에 예약된 시점보다 더 오랫동안 실행되는 프로세스는 누락될 가능성이 있다. 이러한 제약사항을 처리하기 위한 방법으로 일자별로 예약일정을 변칙적으로 운영할 수는 있다. 월요일과 수요일은 오전 6시에 시작하여 15분 간격으로 5분동안 추적작업을 진행하는 것으로 설정하고 화요일과 목요일은 오전 6시 07분에 시작할 수 있다. 프로세스를 랜덤하게 처리하지 못하기는 하지만 적어도 다양성이 존재하도록 설계상 반영은 필요하게 된다.

가장 이상적인 상황이라면 데이터베이스를 운영환경으로 이행시키기 전에 문제점을 찾아내기 위해 테스트 기간동안 개발 데이터베이스에서만 예약된 추적작업을 설정하면 된다. 하지만 실제 현실상황에서는 테스트 서버에서는 실제 운영환경과 동일한 작업부하상황이 지속되지 않기 때문에 실제 운영 서버에서 예약된 추적작업을 설정하게 된다. 추적하는 이벤트 수나 추적의 대상이 되는 데이터 컬럼을 최소화하면 운영 서버에 추적 작업이 실행됨으로써 발생되는 부하를 최소화할 수 있다. 추적 작업의 부하를 다른 쪽으로 분산시키기 위해서는 연결된 서버를 설정하고 SQL 서버의 추적결과를 연결된 서버의 테이블에 저장하면 된다. 또한 추적 산출물 파일을 네트워크 드라이브상에 저장하는 것도 SQL 서버 디스크 서브시스템에 부하를 줄여줄 수 있는 방법이 될 수 있다.

자동화된 추적작업은 노력을 투자할만한 가치가 있는 작업이다. 테이블에 성능관련 데이터를 수집해 놓게 되면 데이터베이스 서버에 접근하는 애플리케이션에 대한 정보를 쉽게 찾아볼 수 있다. 간단한 쿼리를 통해 1초이상 실행된 프로세스의 수를 체크할 수도 있고 이를 데이터베이스 ID를 기준으로 그룹화하여 데이터베이스에 대해서 가장 오랫동안 실행된 프로세스를 찾아낼 수 있다. [웹 리스트 2]코드에는 Kalen Delaney의 도움을 받아 암호화되어 있는 EventClass의 숫자를 프로필러로 추적을 실행하였을 때처럼 적절한 설명으로 나타날 수 있도록 해석해 줄 수 있도록 테이블을 만들고 EventClass별 지속시간 평균값을 조회할 수 있도록 하는 예제 SQL 문장이 나타나 있다.

앞에서 소개한 단계를 따라하게 되면 어떠한 추적 작업이라도 예약된 작업으로 실행시킬 수 있다. 프로필러에서 추적을 생성한 다음, 저장 프로시저로 해당 SQL 스크립트를 저장하고 이미 설정해 놓은 예약된 작업에 해당 저장 프로시저를 실행할 수 있도록 설정하면 된다. 프로필러는 SQL 서버에 대한 많은 정보를 추적할 수 있게 해주며 잠금획득이나 저장 프로시저의 재컴파일, 데이터베이스 파일 증가 등과 같은 SQL 서버에서 발생하고 있는 프로세스에 대한 정보를 제공해 준다. 프로필러에서 직접 실행하는 것보다 기능상으로 향상된 것은 없지만 추적을 예약된 작업을 수행할 수 있다는 것은 데이터베이스에 대해서 지속적이고 일관성있게 추적정보를 수집할 수 있다는 효과가 있으며 이로 인해 SQL 서버에 대해서 좀 더 많은 것을 알아낼 수 있다는 것에 그 의미가 있다.

[리스트 1] SQL 서버의 추적을 생성하고 실행하는 저장 프로시저

/* 두 개의 매개변수를 선언하고, 파일명칭을 입력하면 추적ID를 반환. */
CREATE PROCEDURE _duration_trace
	@file_name nvarchar(155), -- 주의: 파일확장자는 제외
	@trace_id int output
AS
	DECLARE @rc int
	DECLARE @traceID int
	DECLARE @maxfilesize bigint
	SET @maxfilesize = 5

	/* "InsertFileNameHere"부분을 전달받은 파일명으로 치환 */
	EXEC @rc = sp_trace_create @traceID output, 0, @file_name, @maxfilesize, NULL

	-- goto 구문 제거
	IF (@rc != 0)
		RAISERROR ('Error with the sp_trace_create', 16,1)
	ELSE
	BEGIN
		DECLARE @on bit
		SET @on = 1
		-- 10 = RPC:Completed
		-- 12 = SQL:BatchCompleted
		/* sp_trace_setevent 저장 프로시저에 대한 자세한 정보는 SQL Server 온라인 도움말 참조 */
		EXEC sp_trace_setevent @traceID, 10, 1, @on	--1 = 텍스트 데이터
		EXEC sp_trace_setevent @traceID, 10, 3, @on	--3 = 데이터베이스ID
		EXEC sp_trace_setevent @traceID, 10, 13, @on	--13 = 동작시간
		EXEC sp_trace_setevent @traceID, 10, 14, @on	--14 = 시작시간
		EXEC sp_trace_setevent @traceID, 10, 15, @on	--15 = 종료시간
		EXEC sp_trace_setevent @traceID, 10, 16, @on	--16 = 읽기수
		EXEC sp_trace_setevent @traceID, 10, 17, @on	--17 = 쓰기수
		EXEC sp_trace_setevent @traceID, 12, 1, @on
		EXEC sp_trace_setevent @traceID, 12, 3, @on
		EXEC sp_trace_setevent @traceID, 12, 13, @on
		EXEC sp_trace_setevent @traceID, 12, 14, @on
		EXEC sp_trace_setevent @traceID, 12, 15, @on
		EXEC sp_trace_setevent @traceID, 12, 16, @on
		EXEC sp_trace_setevent @traceID, 12, 17, @on

		-- 필터 설정
		DECLARE @intfilter int
		DECLARE @bigintfilter bigint

		EXEC sp_trace_setfilter @traceID, 10, 0, 7, N'SQL Profiler'
		SET @bigintfilter = 1000
		EXEC sp_trace_setfilter @traceID, 13, 0, 4, @bigintfilter

		-- 추적을 실행하기 위해 추적 상태 설정
		EXEC sp_trace_setstatus @traceID, 1
		SET @trace_id = @traceID	-- 추적ID를 반환 
	END

[리스트 2] 추적을 중단하는 작업단계와 추적결과를 SQL 서버 테이블로 업로드하는 코드

-- 작업 2, 단계 1
-- 실행 중인 추적을 중단하고 종료하는 작업단계

DECLARE @trace_ID int, @path_file nvarchar(200)
SELECT @path_file=output_path_file FROM tracer WHERE tid = 1

SELECT @trace_ID = traceid FROM ::fn_trace_getinfo(0)
WHERE property = 2 AND value = @path_file

EXEC sp_trace_setstatus @trace_ID, 0	--<< 중단
EXEC sp_trace_setstatus @trace_ID, 2	--<< 종료

-- 작업 2, 단계 2
DECLARE @path_file_ext nvarchar(200)
SELECT @path_file_ext=output_path_file_ext FROM tracer WHERE tid = 1

INSERT mytrace
SELECT *
FROM ::fn_trace_gettable(@path_file_ext, default)

-- 작업 2, 단계 3
DECLARE @path_file_ext nvarchar(200), @cmd sysname
SELECT @path_file_ext=output_path_file_ext FROM tracer WHERE tid = 1

SET @cmd = 'del ' + @path_file_ext
EXEC master.dbo.xp_cmdshell @cmd

   최종 수정일 : 2004년 12월 15일