How to use SQL store procedure to trace SQL performance instead of SQL profiler

1. Open SQL profiler and selet the event that you want to monitor.

 

2.Save the SQL profiler as a file to C:\perf\trace

3. Open the file C:\perf\Trace in SQL management studio, and edit them as below:

DECLARE @rc INT

DECLARE @TraceID INT

DECLARE @MaxFileSize BIGINT

DECLARE @OutputFileName NVARCHAR(256)

SET @MaxFileSize = 100

SET @OutputFileName = 'C:\prf\trace' +

    CONVERT(VARCHAR(20),GETDATE(),112)+

    REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

 

SET @EndTime =DATEADD(mi,30,getdate())

exec @rc =sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime

-- Set the events and columns

declare @on bit

set @on = 1

exec sp_trace_setevent@TraceID, 10, 16, @on

exec sp_trace_setevent@TraceID, 10, 1,  @on

exec sp_trace_setevent@TraceID, 10, 17, @on

exec sp_trace_setevent@TraceID, 10, 18, @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, 12, 16, @on

exec sp_trace_setevent@TraceID, 12, 1,  @on

exec sp_trace_setevent@TraceID, 12, 17, @on

exec sp_trace_setevent@TraceID, 12, 14, @on

exec sp_trace_setevent@TraceID, 12, 18, @on

exec sp_trace_setevent@TraceID, 12, 12, @on

exec sp_trace_setevent@TraceID, 12, 13, @on

-- Set the trace status to start

exec sp_trace_setstatus@TraceID, 1

 

4. execute the above  query in SQL management studio

 

5. Monitor the trace running status:

 

select *fromsys.traces ---> To get all trace status

 

exec  sp_trace_setstatus3,0 ---> To Stop the Trace ID 3

 

exec  sp_trace_setstatus3,1  ---> To Start the Trace ID 3

 

exec sp_trace_setstatus3,2  ---> To deletethe Trace ID 3