For a DBA, SQL profiler is a great tool to trouble shoot performance issues. It is a pain to configure a trace and analyse it using the profiler GUI . I usually use server side trace which can be configured by running set of scripts. Server side traces are much lighter as it does not need to transfer data over the network.In this post, let us discuss about configuring server side trace.
There are three steps in creating server side trace.
- Defining a trace.
- Defining the events and columns to captured.
- Defining the filter condition.
- Start the trace.
Please find below the script used to configure a server side trace.Refer BOL for more option about adding events,columns and filter condition.
/****************************************************************************************
STEP 1 : DEFINING THE TRACE
***************************************************************************************/
SET NOCOUNT ON;
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @MaxFileSize BIGINT
DECLARE @OutputFileName NVARCHAR(256)
SET @MaxFileSize = 1024
--Replace The H:\MyTraces with a valid folder in your environment
SET @OutputFileName = 'D:\MyTraces\FileTrace' + CONVERT(VARCHAR(20), GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
--sp_trace_create @traceid,@options,@tracefile,@maxfilesize,@stoptime ,@filecount EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @OutputFileName, @MaxFileSize, NULL,5
/****************************************************************************************
STEP 2 : DEFINING THE EVENT AND COLUMNS
*****************************************************************************************/
DECLARE @Status bit
SET @Status = 1
--sp_trace_setevent @traceid ,@eventid ,@columnid,@on
--RPC:Completed event
EXEC sp_trace_setevent @TraceID, 10, 16, @Status
EXEC sp_trace_setevent @TraceID, 10, 1, @Status
EXEC sp_trace_setevent @TraceID, 10, 17, @Status
EXEC sp_trace_setevent @TraceID, 10, 14, @Status
EXEC sp_trace_setevent @TraceID, 10, 18, @Status
EXEC sp_trace_setevent @TraceID, 10, 12, @Status
EXEC sp_trace_setevent @TraceID, 10, 13, @Status
EXEC sp_trace_setevent @TraceID, 10, 8, @Status
EXEC sp_trace_setevent @TraceID, 10, 10, @Status
EXEC sp_trace_setevent @TraceID, 10, 11, @Status
EXEC sp_trace_setevent @TraceID, 10, 35, @Status
--SQL:BatchCompleted event
EXEC sp_trace_setevent @TraceID, 12, 16, @Status
EXEC sp_trace_setevent @TraceID, 12, 1, @Status
EXEC sp_trace_setevent @TraceID, 12, 17, @Status
EXEC sp_trace_setevent @TraceID, 12, 14, @Status
EXEC sp_trace_setevent @TraceID, 12, 18, @Status
EXEC sp_trace_setevent @TraceID, 12, 12, @Status
EXEC sp_trace_setevent @TraceID, 12, 13, @Status
EXEC sp_trace_setevent @TraceID, 12, 8, @Status
EXEC sp_trace_setevent @TraceID, 12, 10, @Status
EXEC sp_trace_setevent @TraceID, 12, 11, @Status
EXEC sp_trace_setevent @TraceID, 12, 35, @Status
/**************************************************************************************** STEP 3 : DEFINING THE Filter condition
*****************************************************************************************/
--sp_trace_setfilter @traceid ,@columnid,@logical_operator,@comparison_operator,@value
EXEC sp_trace_setfilter @TraceID,8,0,0,N'MyAppServer' --Hostname
EXEC sp_trace_setfilter @TraceID,35,0,0,N'MyDB' --Database name
EXEC sp_trace_setfilter @TraceID,11,0,0,N'MyAppUser' --SQL login
/****************************************************************************************
STEP 4 : Start the trace
*****************************************************************************************/
EXEC sp_trace_setstatus @TraceID, 1
/****************************************************************************************
Display the trace Id and traceFilename
*****************************************************************************************/
SELECT @TraceID,@OutputFileName
Now the trace is running and you can verify the currently running trace using the below query
SELECT * FROM ::fn_trace_getinfo(NULL)
Once it ran for the desired time , you can stop the trace using the below script
