2014.04.25 SQL2014 버젼 추가
-- #######################################################
-- ReadTrace 를 위한 프로시져 만들기
-- #######################################################
use master
go
if object_id ('usp_Profiler_Baseline_Start') is not null
drop proc usp_Profiler_Baseline_Start
go
create PROC dbo.usp_Profiler_Baseline_Start
@DateTime datetime -- 기한
, @maxfilesize bigint -- 최대 용량
, @folder nvarchar(245)
, @time datetime -- 추적시작시각
AS
-- 반드시 필요한 항목을 수정 적용할 것, 파일이 존재하면 오류가 발생(오류: 12)
-- Create a Queue
declare @rc int
declare @TraceID int
IF @DateTime IS NULL
SET @DateTime = getdate() + 1 -- 디폴트 내일
IF @maxfilesize IS NULL
SET @maxfilesize = 5 -- 디폴트: 5 MB
-- 추가 내용, 파일명에 .trc 가 자동으로 붙는다
declare @file nvarchar(245)
set @file = @folder
+ 'ProfilerBaseline'
+ replace(@@SERVERNAME, '\', '$')
+ convert(char(8),getdate(),112)
-- 중요. 특정 시각까지 대기 후 시작
WAITFOR TIME @time
PRINT '추적 시작'
-- rollover 안함: 0
exec @rc = sp_trace_create @TraceID output, 2, @file, @maxfilesize, @Datetime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @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
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 16, 1, @on
exec sp_trace_setevent @TraceID, 16, 3, @on
exec sp_trace_setevent @TraceID, 16, 8, @on
exec sp_trace_setevent @TraceID, 16, 10, @on
exec sp_trace_setevent @TraceID, 16, 11, @on
exec sp_trace_setevent @TraceID, 16, 12, @on
exec sp_trace_setevent @TraceID, 16, 13, @on
exec sp_trace_setevent @TraceID, 16, 14, @on
exec sp_trace_setevent @TraceID, 16, 16, @on
exec sp_trace_setevent @TraceID, 16, 17, @on
exec sp_trace_setevent @TraceID, 16, 18, @on
exec sp_trace_setevent @TraceID, 21, 1, @on
exec sp_trace_setevent @TraceID, 21, 3, @on
exec sp_trace_setevent @TraceID, 21, 8, @on
exec sp_trace_setevent @TraceID, 21, 10, @on
exec sp_trace_setevent @TraceID, 21, 11, @on
exec sp_trace_setevent @TraceID, 21, 12, @on
exec sp_trace_setevent @TraceID, 21, 13, @on
exec sp_trace_setevent @TraceID, 21, 14, @on
exec sp_trace_setevent @TraceID, 21, 16, @on
exec sp_trace_setevent @TraceID, 21, 17, @on
exec sp_trace_setevent @TraceID, 21, 18, @on
exec sp_trace_setevent @TraceID, 22, 1, @on
exec sp_trace_setevent @TraceID, 22, 3, @on
exec sp_trace_setevent @TraceID, 22, 8, @on
exec sp_trace_setevent @TraceID, 22, 10, @on
exec sp_trace_setevent @TraceID, 22, 11, @on
exec sp_trace_setevent @TraceID, 22, 12, @on
exec sp_trace_setevent @TraceID, 22, 13, @on
exec sp_trace_setevent @TraceID, 22, 14, @on
exec sp_trace_setevent @TraceID, 22, 16, @on
exec sp_trace_setevent @TraceID, 22, 17, @on
exec sp_trace_setevent @TraceID, 22, 18, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 3, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 16, @on
exec sp_trace_setevent @TraceID, 25, 17, @on
exec sp_trace_setevent @TraceID, 25, 18, @on
exec sp_trace_setevent @TraceID, 33, 1, @on
exec sp_trace_setevent @TraceID, 33, 3, @on
exec sp_trace_setevent @TraceID, 33, 8, @on
exec sp_trace_setevent @TraceID, 33, 10, @on
exec sp_trace_setevent @TraceID, 33, 11, @on
exec sp_trace_setevent @TraceID, 33, 12, @on
exec sp_trace_setevent @TraceID, 33, 13, @on
exec sp_trace_setevent @TraceID, 33, 14, @on
exec sp_trace_setevent @TraceID, 33, 16, @on
exec sp_trace_setevent @TraceID, 33, 17, @on
exec sp_trace_setevent @TraceID, 33, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 3, @on
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 13, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 15, @on
exec sp_trace_setevent @TraceID, 40, 16, @on
exec sp_trace_setevent @TraceID, 40, 17, @on
exec sp_trace_setevent @TraceID, 40, 18, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 13, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 15, @on
exec sp_trace_setevent @TraceID, 42, 16, @on
exec sp_trace_setevent @TraceID, 42, 17, @on
exec sp_trace_setevent @TraceID, 42, 18, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on
exec sp_trace_setevent @TraceID, 53, 1, @on
exec sp_trace_setevent @TraceID, 53, 3, @on
exec sp_trace_setevent @TraceID, 53, 8, @on
exec sp_trace_setevent @TraceID, 53, 10, @on
exec sp_trace_setevent @TraceID, 53, 11, @on
exec sp_trace_setevent @TraceID, 53, 12, @on
exec sp_trace_setevent @TraceID, 53, 13, @on
exec sp_trace_setevent @TraceID, 53, 14, @on
exec sp_trace_setevent @TraceID, 53, 16, @on
exec sp_trace_setevent @TraceID, 53, 17, @on
exec sp_trace_setevent @TraceID, 53, 18, @on
exec sp_trace_setevent @TraceID, 55, 1, @on
exec sp_trace_setevent @TraceID, 55, 3, @on
exec sp_trace_setevent @TraceID, 55, 8, @on
exec sp_trace_setevent @TraceID, 55, 10, @on
exec sp_trace_setevent @TraceID, 55, 11, @on
exec sp_trace_setevent @TraceID, 55, 12, @on
exec sp_trace_setevent @TraceID, 55, 13, @on
exec sp_trace_setevent @TraceID, 55, 14, @on
exec sp_trace_setevent @TraceID, 55, 16, @on
exec sp_trace_setevent @TraceID, 55, 17, @on
exec sp_trace_setevent @TraceID, 55, 18, @on
exec sp_trace_setevent @TraceID, 61, 1, @on
exec sp_trace_setevent @TraceID, 61, 3, @on
exec sp_trace_setevent @TraceID, 61, 8, @on
exec sp_trace_setevent @TraceID, 61, 10, @on
exec sp_trace_setevent @TraceID, 61, 11, @on
exec sp_trace_setevent @TraceID, 61, 12, @on
exec sp_trace_setevent @TraceID, 61, 13, @on
exec sp_trace_setevent @TraceID, 61, 14, @on
exec sp_trace_setevent @TraceID, 61, 16, @on
exec sp_trace_setevent @TraceID, 61, 17, @on
exec sp_trace_setevent @TraceID, 61, 18, @on
exec sp_trace_setevent @TraceID, 67, 1, @on
exec sp_trace_setevent @TraceID, 67, 3, @on
exec sp_trace_setevent @TraceID, 67, 8, @on
exec sp_trace_setevent @TraceID, 67, 10, @on
exec sp_trace_setevent @TraceID, 67, 11, @on
exec sp_trace_setevent @TraceID, 67, 12, @on
exec sp_trace_setevent @TraceID, 67, 13, @on
exec sp_trace_setevent @TraceID, 67, 14, @on
exec sp_trace_setevent @TraceID, 67, 16, @on
exec sp_trace_setevent @TraceID, 67, 17, @on
exec sp_trace_setevent @TraceID, 67, 18, @on
exec sp_trace_setevent @TraceID, 68, 1, @on
exec sp_trace_setevent @TraceID, 68, 3, @on
exec sp_trace_setevent @TraceID, 68, 8, @on
exec sp_trace_setevent @TraceID, 68, 10, @on
exec sp_trace_setevent @TraceID, 68, 11, @on
exec sp_trace_setevent @TraceID, 68, 12, @on
exec sp_trace_setevent @TraceID, 68, 13, @on
exec sp_trace_setevent @TraceID, 68, 14, @on
exec sp_trace_setevent @TraceID, 68, 15, @on
exec sp_trace_setevent @TraceID, 68, 16, @on
exec sp_trace_setevent @TraceID, 68, 17, @on
exec sp_trace_setevent @TraceID, 68, 18, @on
exec sp_trace_setevent @TraceID, 69, 1, @on
exec sp_trace_setevent @TraceID, 69, 3, @on
exec sp_trace_setevent @TraceID, 69, 8, @on
exec sp_trace_setevent @TraceID, 69, 10, @on
exec sp_trace_setevent @TraceID, 69, 11, @on
exec sp_trace_setevent @TraceID, 69, 12, @on
exec sp_trace_setevent @TraceID, 69, 13, @on
exec sp_trace_setevent @TraceID, 69, 14, @on
exec sp_trace_setevent @TraceID, 69, 16, @on
exec sp_trace_setevent @TraceID, 69, 17, @on
exec sp_trace_setevent @TraceID, 69, 18, @on
exec sp_trace_setevent @TraceID, 71, 1, @on
exec sp_trace_setevent @TraceID, 71, 3, @on
exec sp_trace_setevent @TraceID, 71, 8, @on
exec sp_trace_setevent @TraceID, 71, 10, @on
exec sp_trace_setevent @TraceID, 71, 11, @on
exec sp_trace_setevent @TraceID, 71, 12, @on
exec sp_trace_setevent @TraceID, 71, 13, @on
exec sp_trace_setevent @TraceID, 71, 14, @on
exec sp_trace_setevent @TraceID, 71, 16, @on
exec sp_trace_setevent @TraceID, 71, 17, @on
exec sp_trace_setevent @TraceID, 71, 18, @on
exec sp_trace_setevent @TraceID, 72, 1, @on
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 10, @on
exec sp_trace_setevent @TraceID, 72, 11, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 13, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 16, @on
exec sp_trace_setevent @TraceID, 72, 17, @on
exec sp_trace_setevent @TraceID, 72, 18, @on
exec sp_trace_setevent @TraceID, 74, 1, @on
exec sp_trace_setevent @TraceID, 74, 3, @on
exec sp_trace_setevent @TraceID, 74, 8, @on
exec sp_trace_setevent @TraceID, 74, 10, @on
exec sp_trace_setevent @TraceID, 74, 11, @on
exec sp_trace_setevent @TraceID, 74, 12, @on
exec sp_trace_setevent @TraceID, 74, 13, @on
exec sp_trace_setevent @TraceID, 74, 14, @on
exec sp_trace_setevent @TraceID, 74, 16, @on
exec sp_trace_setevent @TraceID, 74, 17, @on
exec sp_trace_setevent @TraceID, 74, 18, @on
exec sp_trace_setevent @TraceID, 76, 1, @on
exec sp_trace_setevent @TraceID, 76, 3, @on
exec sp_trace_setevent @TraceID, 76, 8, @on
exec sp_trace_setevent @TraceID, 76, 10, @on
exec sp_trace_setevent @TraceID, 76, 11, @on
exec sp_trace_setevent @TraceID, 76, 12, @on
exec sp_trace_setevent @TraceID, 76, 13, @on
exec sp_trace_setevent @TraceID, 76, 14, @on
exec sp_trace_setevent @TraceID, 76, 16, @on
exec sp_trace_setevent @TraceID, 76, 17, @on
exec sp_trace_setevent @TraceID, 76, 18, @on
exec sp_trace_setevent @TraceID, 79, 1, @on
exec sp_trace_setevent @TraceID, 79, 3, @on
exec sp_trace_setevent @TraceID, 79, 8, @on
exec sp_trace_setevent @TraceID, 79, 10, @on
exec sp_trace_setevent @TraceID, 79, 11, @on
exec sp_trace_setevent @TraceID, 79, 12, @on
exec sp_trace_setevent @TraceID, 79, 13, @on
exec sp_trace_setevent @TraceID, 79, 14, @on
exec sp_trace_setevent @TraceID, 79, 16, @on
exec sp_trace_setevent @TraceID, 79, 17, @on
exec sp_trace_setevent @TraceID, 79, 18, @on
exec sp_trace_setevent @TraceID, 80, 1, @on
exec sp_trace_setevent @TraceID, 80, 3, @on
exec sp_trace_setevent @TraceID, 80, 8, @on
exec sp_trace_setevent @TraceID, 80, 10, @on
exec sp_trace_setevent @TraceID, 80, 11, @on
exec sp_trace_setevent @TraceID, 80, 12, @on
exec sp_trace_setevent @TraceID, 80, 13, @on
exec sp_trace_setevent @TraceID, 80, 14, @on
exec sp_trace_setevent @TraceID, 80, 16, @on
exec sp_trace_setevent @TraceID, 80, 17, @on
exec sp_trace_setevent @TraceID, 80, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- 필터 조건
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL 프로필러'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'MS SQLEM'
-- exec sp_trace_setfilter @TraceID, 3, 0,0,5 -- 특정데이터 베이스만 잡을때
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select '추적번호: ', TraceID=@TraceID
goto finish
error:
select '오류:', ErrorCode=@rc
finish:
go
-- #######################################################
-- 프로파일링 시작하기
-- #######################################################
declare @t datetime, @t1 datetime
set @t = dateadd(ss,2,getdate())
set @t1 = dateadd(mi,3,getdate()) -- 3분간 모니터링 (아주 안쓰는 시간대에.....)
-- set @t = convert(char(8), getdate(),112) +' 10:00:00'
-- set @t1 = convert(char(8), getdate(),112) +' 11:00:00'
EXEC dbo.usp_Profiler_Baseline_Start
@DateTime = @t1 -- 기한
, @maxfilesize = 100 -- 최대파일크기(MB)
, @folder = 'c:\temp\' -- 주의. 경로에공백이있어서는안된다.
, @time = @t -- 시작시각
-- #######################################################
-- 프로파일링 이벤트 관찰하기
-- #######################################################
select * from ::fn_trace_getinfo (null)
-- #######################################################
-- 프로파일링 중지시키기
-- #######################################################
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2
-- #######################################################
-- (콘솔) 로컬로 트레이스 파일을 옮긴후 데이터베이스 만들고나서 실행합니다.
-- #######################################################
cd "Program Files\Microsoft Corporation\RMLUtils"
ReadTrace -I"C:\Temp\ProfilerBaselineKIMMS.trc" -o"c:\temp\kimms" -S(local) -dkimmsOut -- 만들데이터베이스 kimmsOut