Using Sql profiler to check for performance issue .. continue

To use the server side sql tracing, we can use the following

DECLARE @TraceID int

Declare @tracefilename varchar(50)

set @tracefilename = 'C:\Temp\ServerSide_Trace' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')

EXEC CreateTrace

@tracefilename,

@OutputTraceID = @TraceID OUT

-- stored procedures

EXEC AddEvent

@TraceID,

'SP:Completed',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

--- sqls

EXEC AddEvent

@TraceID,

'SQL:StmtCompleted',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

-- only include procedures that has run for more than 10 seconds.

EXEC AddFilter

@TraceID,

'Duration',

10000,

'>='

--- exclude system run ned stored procedures and sqls

EXEC AddFilter

@TraceID,

'ObjectID',

100,

'>='

EXEC StartTrace @TraceID

GO


To clear the server side tracing,

EXEC StopTrace 1

EXEC ClearTrace 1