Warning - this is quite a LONG post.
Hi all,
Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
want to do some tracing in our production environment, and have jobs set up
to automaGically run the traces, so I am building the traces in an SP, via
SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
SQL scheduled jobs, and will happily create the trace files. I have another
SP to then copy these files to another server, and automaGically import them
in some tables for analyzing.
All good and well, so far, EXCEPT that while my trace get's created with no
errors, when I look at the trace data, it seems that the filters are not
being applied. When I open it up from Profiler, I can see the trace
properties, and the Events and Data columns are all correct, but there are n
o
Filters. Anyone see where I am going wrong?
So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
The SP
if exists (select 1 from sysobjects where name =
'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
required by SP_CREATE_TRACE. The File to be created. @.StopTime
datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
required by SP_CREATE_TRACE. The maximum size per
file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitat
e
the creation of profiler traces that write to files.-- -- Example:-- exec
Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000
','2004-12-28-10:05:00.000',100,500-
-- Possible
future additions:-- 1> simple enhancement to specify DB to store data, and
table.-- 2> accepting a parameter instead of apply the default .trc. Use th
e
.trc as default-- 3> implementing default value's for dir's, so that it ca
n
run without parm's ... good or bad...- Change Control: version 1 - Regan
Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
generation of limited data, -- filtering on duration-- Added Output
parametre @.TracId to return for lookup on trace. --AS--Declare Control
Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Optio
n
intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
@.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
that when the max_file_size is reached, the current trace file is closed and
a new file is created.--Set Trace Filter to exclude System Ids - that is
ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
@.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On =
1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.Stop
Timeselect @.ReturnCode=@.@.Errorif
@.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned whe
n
options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
Cannot create tracefile - check if file already exists, or this trace alread
y
running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
there is not enough memory to perform the specified action.' if @.ReturnCode
=
14 Print 'Error 14 - Invalid stop time. Returned when the stop time
specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
Invalid parameters. Returned when the user supplied incompatible
parameters.' else Print 'Unexpected and Unknown error In creating trace -
Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_seteven
t
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
@.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexe
c
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
Cursor Recompileset @.EventId = 75exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this se
t
is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
@.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_seteven
t
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
= 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId =
97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_seteven
t
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
set @.EventId = 98-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
@.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData-
-
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
= 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
Transaction-- set @.EventId = 50-- exec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
BatchCompletedset @.EventId = 12exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
@.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_seteven
t
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
'ERROR 13 - Out of memory. Returned when there is not enough memory to
perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
The specified Trace Handle is not valid.' else if @.ReturnCode = 8 prin
t
'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknow
n
Error' GoTo ErrorHandlerendexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.Com
parisonOperator,@.Valueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERRO
R
1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is no
t
valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is no
t
allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
= 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
@.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is no
t
enough memory to perform the specified action.' else if @.ReturnCode =
16 print 'ERROR 16 - The function is not valid for this trace.' else Print
'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
--Greater than exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.Com
parisonOperator,@.DurationFilterValue
select
@.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace is currently running. Changing the trace at this time will result in a
n
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Colum
n
is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The specified Comparison Operator is not valid. ' else if @.ReturnCode =
7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
Returned when there is not enough memory to perform the specified
action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
ErrorHandler endendErrorHandler:Return @.ReturnCode
The Code that calls the trace
DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
@.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
@.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
@.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
@.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
@.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize =
100 -- this is the maximum size for a file, in MBset @.DurationFilter =
1000--Set Running Variables--the time the trace will stopset @.RunStopTime =
dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
UNC)set @.FileName =
'C:\Auto_Prof\AutoProf_SP_' +cast(datep
art(yyyy,getdate()) as
char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
char(3)),2) --Months +cast(datepart(d,getdate()) as
char(2))+'_' -- Days +right(cast(datepart(hh,getdate())
+100 as
char(3)),2) -- Hours +right(cast(datepart(mi,getdate()
)+100 as
char(3)),2) --Minutes--Display variablesprint 'File created is :
'+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
varchar(20))--Create trace, writing out to tracefile, until endtimeexec
@.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.Duratio
nFilter,@.TraceID output--Error
Handlingselect @.rcselect @.TraceIDSELECT * FROM
::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
Ta...the data type of the @.durationfiltervalue must correspond with the data type
of the column to be filtered. otherwise it will not work.
unfortunately I was unable to post this into a QA session my connection kept
crashing .
Hope that helps
"Wanderer" wrote:
> Warning - this is quite a LONG post.
> Hi all,
> Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture -
I
> want to do some tracing in our production environment, and have jobs set u
p
> to automaGically run the traces, so I am building the traces in an SP, via
> SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called fr
om
> SQL scheduled jobs, and will happily create the trace files. I have anothe
r
> SP to then copy these files to another server, and automaGically import th
em
> in some tables for analyzing.
> All good and well, so far, EXCEPT that while my trace get's created with n
o
> errors, when I look at the trace data, it seems that the filters are not
> being applied. When I open it up from Profiler, I can see the trace
> properties, and the Events and Data columns are all correct, but there are
no
> Filters. Anyone see where I am going wrong?
> So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> The SP
> if exists (select 1 from sysobjects where name =
> 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
> required by SP_CREATE_TRACE. The File to be created. @.StopTime
> datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt
as
> required by SP_CREATE_TRACE. The maximum size per
> file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> On: 2004-12-28-- Purpose: -- This stored procedure was written to facilit
ate
> the creation of profiler traces that write to files.-- -- Example:-- exe
c
> Up_Auto_Profiler_Perf
> 'C:\TraceFiles\AutoProf_SP_20041228_1000
','2004-12-28-10:05:00.000',100,50
0-- Possible
> future additions:-- 1> simple enhancement to specify DB to store data, a
nd
> table.-- 2> accepting a parameter instead of apply the default .trc. Use
the
> .trc as default-- 3> implementing default value's for dir's, so that it c
an
> run without parm's ... good or bad...- Change Control: version 1 - Regan
> Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2
-
> Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allo
w
> generation of limited data, -- filtering on duration-- Added Outpu
t
> parametre @.TracId to return for lookup on trace. --AS--Declare Control
> Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Opt
ion
> intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
> that when the max_file_size is reached, the current trace file is closed a
nd
> a new file is created.--Set Trace Filter to exclude System Ids - that is
> ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
> than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On =
> 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceI
d
> output,@.Option,@.TraceFile,@.MaxSize,@.Stop
Timeselect @.ReturnCode=@.@.Errorif
> @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned w
hen
> options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12
-
> Cannot create tracefile - check if file already exists, or this trace alre
ady
> running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned wh
en
> there is not enough memory to perform the specified action.' if @.ReturnCod
e =
> 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> Invalid parameters. Returned when the user supplied incompatible
> parameters.' else Print 'Unexpected and Unknown error In creating trace -
> Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setev
ent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClasse
xec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
> Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this
set
> is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
> @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataex
ec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setev
ent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.Event
Id
> = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_seteven
t
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
> Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId =
> 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setev
ent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics-
-
> set @.EventId = 98-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- s
et
> @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDat
a--
> exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_seteven
t
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.Event
Id
> = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_seteven
t
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
> Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
> BatchCompletedset @.EventId = 12exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- se
t
> @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- ex
ec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setev
ent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> The specified Trace Handle is not valid.' else if @.ReturnCode = 8 pr
int
> 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unkn
own
> Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.Com
parisonOperator,@.Valueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ER
ROR
> 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
not
> valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is
not
> allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 pri
nt
> 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCo
de
> = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is
not
> enough memory to perform the specified action.' else if @.ReturnCode =
> 16 print 'ERROR 16 - The function is not valid for this trace.' else Pri
nt
> 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> --Greater than exec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.Com
parisonOperator,@.DurationFilterVal
ue select
> @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - Th
e
> trace is currently running. Changing the trace at this time will result in
an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is
> not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Col
umn
> is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6
-
> The specified Comparison Operator is not valid. ' else if @.ReturnCode =
> 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
if
> @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> Returned when there is not enough memory to perform the specified
> action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is no
t
> valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> ErrorHandler endendErrorHandler:Return @.ReturnCode
>
> The Code that calls the trace
> DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLAR
E
> @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesse
t
> @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize =
> 100 -- this is the maximum size for a file, in MBset @.DurationFilter =
> 1000--Set Running Variables--the time the trace will stopset @.RunStopTime
=
> dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (no
t
> UNC)set @.FileName =
> 'C:\Auto_Prof\AutoProf_SP_' +cast(datep
art(yyyy,getdate()) as
> char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> char(3)),2) --Months +cast(datepart(d,getdate()) as
> char(2))+'_' -- Days +right(cast(datepart(hh,getdate())
+100 as
> char(3)),2) -- Hours +right(cast(datepart(mi,getdate()
)+100 as
> char(3)),2) --Minutes--Display variablesprint 'File created is :
> '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> @.Rc=Up_Auto_Profiler_Perf
> @.FileName,@.RunStopTime,@.MaxSize,@.Duratio
nFilter,@.TraceID output--Error
> Handlingselect @.rcselect @.TraceIDSELECT * FROM
> ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
> Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
>
> Ta...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment