SQL Server DB 인스턴스의 서버 측 트레이스 실행

스크립트를 작성하여 서버 측 트레이스를 생성하는 것은 복잡할 뿐만 아니라 이 문서의 범위에서 벗어납니다. 따라서 여기서는 예제로 사용할 수 있는 샘플 스크립트만 다룹니다. 클라이언트 측 트레이스와 마찬가지로 이 트레이스의 목적 역시 데이터베이스 엔진 튜닝 어드바이저를 사용해 열 수 있도록 워크로드 파일 또는 트레이스를 생성하는 데 있습니다.

다음은 서버 측 트레이스를 시작하여 세부 정보를 워크 로드에 캡처하기 위해 요약된 예제 스크립트입니다. 트레이스가 처음에는 D:\RDSDBDATA\Log 디렉터리의 RDSTrace.trc 파일에 저장되지만 100MB마다 롤오버되어 이후부터는 RDSTrace_1.trc, RDSTrace_2.trc 등의 이름으로 트레이스 파일이 저장됩니다.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

다음은 트레이스를 중단하는 스크립트 예제입니다. 단, 이전 스크립트에서 생성된 트레이스는 명시적으로 트레이스가 중단되거나, 프로세스의 디스크 공간이 부족해질 때까지 계속 실행됩니다.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

서버 측 트레이스 결과는 데이터베이스 테이블로 저장한 후 fn_trace_gettable 함수를 이용하면 튜닝 어드바이저의 워크로드로 사용할 수 있습니다. 다음 명령은 RDSTrace_1.trc 등의 모든 롤오버 파일을 포함해 D:\rdsdbdata\Log 디렉터리에서 RDSTrace.trc라는 이름의 모든 파일 결과를 현재 데이터베이스에서 RDSTrace라는 이름의 테이블로 로드합니다.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

예를 들어 RDSTrace_1.trc 같이 특정 롤오버 파일을 테이블에 저장하려면 롤오버 파일의 이름을 지정한 다음 fn_trace_gettable의 마지막 파라미터를 default가 아닌 1로 변경합니다.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);