SQL SERVER 프로파일 쿼리로 실행하기

오늘은 SQL SERVER 프로파일링 하는 방법에 대해 포스팅하겠습니다. 

SQL PROFILE 프로그램을 이용하여 프로파일링을 하는 방법도 있겠지만, 장시간 사용시 리소스가 과다하게 사용될 수 있으므로, 아래와 같이 T-SQL 형태로 수집하려고 합니다. 

 

아래 내용도 SQL SERVER DBA 면접시 많이 나오는 질문중에 하나이므로, 이해하는 것이 좋을 것 같습니다. 

 

프로파일링 SP 만들기 

복잡해보이지만, 3가지 중요한 SP만 기억하면 쉽게 이해할 수 있습니다. 

이 가운데 sp_trace_setfilter 를 통해 어떤 항목을 추적할 것인지, 설정값을 추가해주면 됩니다. 

 

sp_trace_create @TraceId OUT, @Options, @TraceFileName,@MaxFileSize, @StopTime -> 추적 큐 생성 

EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter -> 필터 추가 

EXEC sp_trace_setstatus @TraceId, 1 -> 추적 시작 

USE master
GO
CREATE PROCEDURE sp_trace_start @TraceFileName sysname=NULL,
@TraceName sysname='trace',
@Options int=2, -- TRACE_FILE_ROLLOVER
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
'10,12',
-- 10 - RPC:Completed
-- 12 - SQL:BatchCompleted
@Cols varchar(300)=
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44',
-- 모든 이벤트 열
@IncludeFilter sysname=NULL,
@ExcludeFilter sysname=NULL
AS
SET NOCOUNT ON
-- 변수 선언
DECLARE @TraceId int
DECLARE @On bit
DECLARE @Rc int
SET @On=1
-- 이벤트와 이벤트 열을 확인한다.
IF @Events IS NULL or @Cols IS NULL BEGIN
PRINT 'No Events or Coloumns.'
RETURN -1
END
-- 파일경로와 파일명을 설정한다.
IF @TraceFileName IS NULL
SELECT @TraceFileName ='C:\Trace\Trace' + CONVERT(CHAR(8),getdate( ),112)
-- 추적 큐를 만든다
EXEC @Rc =sp_trace_create @TraceId OUT, @Options, @TraceFileName,
@MaxFileSize, @StopTime
IF @Rc<>0 BEGIN
PRINT 'Trace not started.'
RETURN @Rc
END
PRINT 'Trace started.'
PRINT 'The trace file name is '+@TraceFileName+'.'
-- 추적할 이벤트 클래스들과 이벤트 열들을 지정한다
DECLARE @i int, @j int, @Event int, @Col int, @Colstring varchar(300)
IF RIGHT(@Events,1)<>','SET @Events=@Events+','
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @Colstring=@Cols
IF RIGHT(@Colstring,1)<>','SET @Colstring=@Colstring+','
SET @j=CHARINDEX(',',@Colstring)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@Colstring,@j-1) AS int)
EXEC sp_trace_setevent @TraceId, @Event, @Col, @On
SET @Colstring=SUBSTRING(@Colstring,@j+1 ,300)
SET @j=CHARINDEX(',',@Colstring)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
-- 필터를 설정한다
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, N'EXEC% sp_%trace%'
IF @IncludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter
IF @ExcludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter
-- 추적을 활성화한다
EXEC sp_trace_setstatus @TraceId, 1
-- 추적을 기록한다. (테이블 사용)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueueList (TraceID int, TraceName varchar(20),
TraceFile sysname)
END
IF EXISTS(SELECT * FROM tempdb..TraceQueueList WHERE TraceName =
@TraceName) BEGIN
UPDATE tempdb..TraceQueueList
SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END
ELSE BEGIN
INSERT tempdb..TraceQueueList
VALUES(@TraceId, @TraceName, @TraceFileName)
END
RETURN 0
GO

추적 시작하기 

use master
go
exec sp_trace_start @TraceFileName = 'C:\Trace\Trace3',@TraceName ='test'

결과 확인하기 

아래와 같이 추적파일이 생성이 완료되었고, 해당파일을 열어봤을때 추적이 잘된 것을 확인할 수 있습니다. 

 

추적 종료하기 

DECLARE @traceID int 

SELECT @traceID = TraceID fROM tempdb..TraceQueueList where TraceName = 'test'

exec sp_trace_setstatus @traceID,1