SQL 포켓북 중에 "SQL Server 2005 초보 DBA를 위한 SQL Server 2005 관리 가이드"라는 책이 있다. 아직도 가끔 이 포켓북을 보면서 참고 할때가 있다. 아직 난 초보 DBA 인듯 하다. 아무튼 이 포켓북에 보면 Transact-SQL 문으로 추적(Trace)를 만드는 부분이 있다. 이번에는 이 부분을 중점으로 적어보려고 한다.
먼저, 추적(Trace)이란? 무엇인가? 추적(Trace)이란, 거창한것이 아니고 우리가 알고 있는 SQL 프로파일러이다. 우리는 이 SQL 프로파일러를 특정 사건(?), 사고가 있을때 사용을 한다. 사실 그렇게 많이 사용해볼 기회도 없었던것 같다. 그래서 이번에 이 포켓북을 참고 삼아 추적(Trace)을 Stored procedure로 만들어서 매일 특정 시간에 추적(Trace)을 돌리고 분석하고자 하는 목적이다.
추적(Trace)을 만드려면 다음과 같은 순서로 만들어야 한다.
- 추적(Trace)을 만드는데 필요한 매개 변수를 사용하여 sp_trace_create를 실행한다.
- 추적(Trace)할 이벤트 및 열을 선택하는 데 필요한 매개 변수를 사용하여 sp_trace_setevent을 실행한다.
- sp_trace_setfilter을 실행하여 필터 또는 필터 조합을 설정한다.
[참고] http://technet.microsoft.com/ko-kr/library/ms188662.aspx
포켓북에 있는 거의 그대로의 Transact-SQL문 그대로를 올리겠다.
-- =============================================
-- Author : 이승연
-- Create date : 2009-06-16
-- Description :
-- =============================================
ALTER PROCEDURE [dbo].[usp_trace_start]
@TraceFileName sysname = NULL -- 파일저장경로및파일이름
, @TraceName sysname = 'trace'
, @option int = 2 -- trace 파일사이즈가풀일때파일롤오버여부2 이면한다. 0이면안한다.
, @MaxFileSize bigint = 5 -- 최고trace 파일사이즈
, @StopTime datetime = NULL -- trace 끝나는시간
, @Events varchar(1000) = '10,11,12,13,14,15,16,21,22,25,33,37,40,41,42,44,43,53,55,61,67,68,69,71,72,74,76,79,80'
/*
Trace Event 설명
10 : RPC(원격프로시저가호출)가완료되면발생
11 : RPC가시작되면발생
12 : Transact-SQL 일괄처리가완료되면발생
13 : Transact-SQL 일괄처리가시작되면발생
14 : 사용자가SQL Server에성공적으로로그인하면발생
15 : 사용자가SQL Server에서로그아웃을하면발생
16 : 클라이언트인터럽트요청이나클라이언트연결이끊어지는등주의이벤트가일어나면발생
21 : 이벤트가windows 응용프로그램로그에기록되었음을나타냄
22 : 오류이벤트가SQL Server 오류로그에기록되었음을나타냄
25 : 다른트랜잭션이소유한리소스에대해호환되지않는잠금을가져오려고시도하여두개의트랜잭션이서로교착상태에있음을나타냄
33 : SQL Server에서예외가발생했음을나타냄
37 : 저장프로시저가다시컴파일되었음을나타냄
40 : Transact-SQL 문이시작되면발생
41 : Transact-SQL 문이완료되면발생
42 : 저장프로시저가사직된때를나타냄
43 : 저장프로시저가완료된때를나타냄
44 : 저장프로시저내의Transact-SQL 문이실행을시작했을때발생
45 : 저장프로시저내의Transact-SQL 문이실행을완료했을때발생
53 : 커서가ODBC, OLE DB 또는DB-Library에의해열린때를나타냄
55 : 버퍼파티션에서처리되지않는해시조인, 해시집계, 해시통합, 해시중복제외등의해시작업이대체계획으로되돌려졌음을나타냄
61 : OLE DB 오류가발생하였음을나타냄
67 : SQL Server 문이나저장프로시저실행중에발생한경고를나타냄
68 : 실행된Transact-SQL 문의계획트리를표시
69 : 정렬작업이메모리에적합하지않음을나타냄
71 : ODBC, OLE DB 또는DB-Library가사용하기위해Transact-SQL문을준비할때발생
72 : ODBC, OLE DB 또는DB-Library가준비된Transact-SQL문을실행할때발생
74 : Transact-SQL 문에서ODBC, OLE DB 또는DB-Library에의해이전에준비된커서를실행할때발생
76 : -
79 : -
80 : -
참고URL : http://technet.microsoft.com/ko-kr/library/ms186265.aspx
*/
, @Cols varchar(300) = '1,3,8,10,11,12,13,14,15,16,17,18,28,35,48'
/*
Trace Event 열ID
3 databaseID : database ID (DB_ID로확인가능한값)
8 HostName : 요청을처음에시작한클라이언트컴퓨터이름
10 ApplicationName : SQL Server 인스턴스에연결한클라이언트응용프로그램의이름
11 LoginName : SQL Server 로그인이름
12 SPID : 프로세스에할당한서버프로세스ID
13 Duration : 이벤트에의해사용된경과시간(마이트로초)
14 StartTime : 이벤트가시작된시간
15 EndTime : 이벤트종료시간
16 Reads : 서버에서수행한논리적디스크읽기수
17 Writes : 서버에서수행한물리적디스트쓰기수
18 CPU : 이벤트에의해사용된CPU 시간(밀리초)
28 ObjectType : 개체유형(테이블, 함수, 저장프로시저등)
35 DatabaseName : 데이터베이스이름
48 RowCounts : 일괄처리의행수입니다
*/
, @IncludeFilter sysname = NULL
, @ExcludeFilter sysname = NULL
AS
BEGIN
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)
--SELECT @TraceFileName = 'C:\Trace\Trace_' + CONVERT(char(8),getdate(), 112)
-- 추척큐를만든다.
EXEC @Rc = sp_trace_create @TraceID out, @option, @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 dbo.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
-- 추척을기록한다. (TempDB의테이블생성사용)
-- 기록하는이유는Trace Stop 시TraceID를알아야하기때문
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 temdb..TraceQueueList SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END ELSE BEGIN
INSERT tempdb..TraceQueueList VALUES (@TraceID, @TraceName, @TraceFileName)
END
END
한줄 한줄 모든것을 설명하려면 끝도 없으니 중요한 부분만 설명을 하겠다. (글쓰는 본인도 까먹기 때문에 중요한 부분만 적어놓으려고 하는것임 +_+..)
먼저, 추적을 만드는 sp_trace_create 구문먼저 살펴보자.
EXEC @Rc = sp_trace_create @TraceID out, @option, @TraceFileName, @MaxFileSize, @StopTime
이 구문은 추적(Trace)을 만드는 구문이다.
- @TraceID : MS-SQL 서버에서 부혀하는 추적(Trace)번호 이다.
- @option : 추적(Trace)에 대한 옵션이다. 2 이면 추적파일에 대한 Rollover 기능을 가는것이고, 4이면 추적 파일이 지정한 Max Size에 도달하면 MS-SQL 시스템을 정지하라는 옵션이며, 8은 블랙박스 기능을 하며 서버가 종료되기 전의 5MB의 기록을 저장하라는 것이다.
- @TraceFileName : 추적을 기록할 파일과 파일 위치를 지정하는 parameter이다.
- @MaxFileSize : 추적 파일의 Max Size을 지정한다. 지정하지 않을시에는 기본 5MB가 지정된다.
- @StopTime : 추적을 중지할 날짜 및 시간을 지정한다.
[참고] http://technet.microsoft.com/ko-kr/library/ms190362.aspx
sp_trace_create 는 이와같은 paremeter를 받고 있으며, sp_trace_create 이 완료된 후의 리턴값으로 아래와 같이 상태를 파악할 수 있다.
- 0 : 오류가 없습니다.
- 1 : 알 수 없는 오류입니다.
- 10 : 잘못된 옵션입니다. 지정한 옵션이 호환되지 않으면 반환됩니다.
- 12 : 파일이 생성되지 않았습니다.
- 13 : 메모리가 부족합니다. 지정한 동작을 수행할 메모리가 충분하지 않으면 반환됩니다.
- 14 : 잘못된 중지 시간입니다. 지정한 중지 시간이 이미 지난 경우 반환합니다.
- 15 : 잘못된 매개 변수입니다. 사용자가 호환되지 않는 매개 변수를 제공하면 반환됩니다.
[참고] http://technet.microsoft.com/ko-kr/library/ms190362.aspx
다음으로 sp_trace_setevent 에 대하여 설명을 하겠다. sp_trace_setevent 는 실제로 추적될 이벤트와 그 이벤트에 대한 열을 지정하는 시스템 저장 프로시저 이다. 이벤트와 이벤트열에 대하서는 위 Transact-SQL문에 설명이 되여 있고, 더욱 자세한 설명을 원하면 http://technet.microsoft.com/ko-kr/library/ms186265.aspx 해당 링크의 문서를 보면 될 것 같다.
sp_trace_setfilter에 대하여 알아보자. sp_trace_setfilter는 이벤트열에 대하여 조건을 거는 것이다. 간단하게 예를 들어 이벤트열 중에 duration이라는 열이 있다 이 열은 지연시간을 말하는데 이 값이 100 보다 큰것만 추적해라 이런식으로 조건을 걸 수 있는것을 말한다.
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
구문은 이와 같고, parameter를 살펴보자.
- @TraceID : sp_trace_create으로 만들었던 추적(Trace) ID
- 10 : 필터를 적용할 이벤트열의 번호, 10번이면 Application Name 이 된다.
- 0 : 0이면 AND 연산자 적용, 1이면 OR 연산자 적용
- 7 : 비교 연산자를 적용
----------------------------
0 : = (같음)
1 : <> (같지않음)
2 : > (보다 큼)
3 : < (보다 작음)
4 : >= (크거나 같음)
5 : <= (작거나 같음)
6 : LIKE
7 : NOT LIKE - N'SQL Profiler' : 필터링 할 값을 지정, 참고사항은 int 값에 지정할때는 필터의 값도 int형이여야 하고 varchar는 최대 8000자 까지 가능하다는 것을 참고하면 되겠다.
해당 구문의 반환되는 코드 값은
- 0 : 오류가 없습니다.
- 1 : 알 수 없는 오류입니다.
- 2 : 추적이 현재 실행 중입니다. 지금 추적을 변경하면 오류가 발생합니다.
- 4 : 지정한 열이 유효하지 않습니다.
- 5 : 지정한 열을 필터링할 수 없습니다. 이 값은 sp_trace_serfilter에서만 반환됩니다.
- 6 : 지정한 비교 연산자가 유효하지 않습니다.
- 7 : 지정한 논리 연산자가 유효하지 않습니다.
- 9 : 지정한 추적 핸들이 유효하지 않습니다.
- 13 : 메모리가 부족합니다. 지정한 동작을 수행할 메모리가 충분하지 않으면 반환됩니다.
- 16 : 함수가 이 추적에 유효하지 않습니다.
즉, 해당 EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
[참고] http://technet.microsoft.com/ko-kr/library/ms174404.aspx
마지막으로 sp_trace_setstatus 문을 살펴보겠다. sp_trace_setstatus는 추적(Trace) 상태를 결정하는 것으로 추적(Trace) 시작과 종료를 명령할 수 있다.
EXEC sp_trace_setstatus @TraceID, 1
- @TraceID : sp_trace_create으로 만들었던 추적(Trace) ID
0 : 지정한 추적을 중지한다.
1 : 지정한 추적을 시작한다.
2 : 지정한 추적을 닫고 서버에서 해당 정의를 삭제한다.
해당 구문의 반환값은
- 0 : 오류가 없습니다.
- 1 : 알 수 없는 오류입니다.
- 8 : 지정한 상태는 유효하지 않습니다.
- 9 : 지정한 추적 핸들이 유효하지 않습니다.
- 13 : 메모리가 부족합니다. 지정한 동작을 수행할 메모리가 충분하지 않으면 반환됩니다.
[참고]http://technet.microsoft.com/ko-kr/library/ms176034.aspx
다음 글에서는 해당 추적을 중지하는 Transact-SQL을 올리겠습니다.
다시한번 말씀드리지만, 해당 Transact-SQL은 SQL 포켓북 중에 "SQL Server 2005 초보 DBA를 위한 SQL Server 2005 관리 가이드"의 p.215쪽에 기록되어 있는 예제 스크립트이며, 제가 개인적으로 기억하기 위해 부연 설명과 함께 포스팅을 한 것입니다.
'MSSQL > Performance' 카테고리의 다른 글
| System Tables and Catalog Views (1) | 2009.10.08 |
|---|---|
| 운영체제 Disk 사용량 보기 (0) | 2009.09.04 |
| MS-SQL File 사용량 보기 (2) | 2009.09.04 |
| Transact-SQL 로 만들어 본 추적(Trace) 관리 저장 프로시저 - (2) (2) | 2009.07.09 |
| 인덱스 재작성 에러 Server: Msg 3628, Level 16, State 1, Line 1 (1) | 2009.06.11 |
