For those who have been following my posts, you would recollect that I had run a 6-part series on SQL Profiler tutorials some time ago. Based on the feedback received, I am now presenting a 4-part extension to the series. In this part, I will describe how the capturing of a SQL Trace can be done in an automated fashion, from the server-side instead of from the client side.

It is quite logical that using the graphical interface for the SQL Profiler will generate a performance overhead on the SQL Server. The primary reasons for this behaviour are:

• All events and trace data needs to be brought over to the tracing client application – this introduces an overhead on the network
• Graphically representing this data introduces a performance overhead in making room for new trace data flowing in from the network

The ideal solution to this is to have as concise and precise trace as possible and to run the trace on the SQL Server.

T-SQL Support for SQL Traces

SQL Server comes with 5 system stored procedures that allow us to monitor performance and activity on the server using T-SQL code on the server itself and also eliminating the need for using graphical tools (Profiler) for data capture, thereby improving overall performance. We will be looking at the 4 most commonly used system stored procedures that allow us to write a SQL Trace using T-SQL.

Creating the trace – sp_trace_create

The first step is, of course, to create a SQL Trace. Once the trace is created, then we can go in and configure the trace, add events to monitor and columns to capture and finally, run the trace. The system stored procedure to create a new SQL Trace is “sp_trace_create”. It accepts the following parameters:

1. @trace_id
   • This is the only OUTPUT parameter, which contains the trace_id assigned to the newly created SQL Trace
2. @options
   • Allows the user to control whether the system should rollover to a new target storage file when the given maximum size is reached, or whether to shut-down the SQL Server in case a trace file cannot be written to
3. @tracefile
   • This parameter specifies the local or UNC path of the file to which the trace output should be written to
4. @maxfilesize
   • Allows the user to set the maximum size to which a trace file will grow before a roll-over or an overwrite happens
5. @stoptime
• Specifies the date and time the trace collection will be stopped
6. @filecount
   • Allows the user to control the maximum number of rollover files, after which an overwrite should happen

An example call to the stored procedure sp_trace_create -  is shown below:

DECLARE @rc INT
DECLARE @TraceID INT
--Create the SQL Trace
EXEC @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
SELECT @rc, @TraceID

Words of caution:

1. Avoid using trace target file names that contain underscores (“_”) in their names. This is because the rollover mechanism versions files using the underscore (“_”)
   • Using underscores (“_”) in the file name will cause rollover mechanism to fail
2. This stored procedure only defines a trace. It does not start, stop or modify an existing trace
3. The parameters of this stored procedure are strongly typed. The stored procedure will error out if called with an incorrect combination of input parameter data types
4. If NULL is specified for @stoptime, the trace runs until it is manually stopped or until the server shuts down

Configuring the trace – sp_trace_setevent

Now that we have the trace defined, it is time for us to configure the trace by setting the events that need to be monitored and the columns that need to be captured. As is the case with the graphical tool (Profiler), the events/columns configurations can be done only on traces that are currently stopped.

As expected, the stored procedure sp_trace_setevent expects the following 4 parameters:

1. @trace_id
   • The @trace_id returned by sp_trace_create
2. @eventid
   • The integer representation of the particular event to capture
3. @columnid
   • The integer representation of the particular data column to capture
4. @on
   • Bit flag indicating whether to capture this event/column or not (corresponds to the check-box on the graphical tool (Profiler))

What this means is that if we are capturing 10 data columns for 1 event, we would need to write 10 sp_trace_setevent statements. Here’s an example:

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 125, 1, @on
exec sp_trace_setevent @TraceID, 125, 3, @on
exec sp_trace_setevent @TraceID, 125, 8, @on
exec sp_trace_setevent @TraceID, 125, 12, @on
exec sp_trace_setevent @TraceID, 125, 14, @on
exec sp_trace_setevent @TraceID, 125, 22, @on
exec sp_trace_setevent @TraceID, 125, 26, @on
exec sp_trace_setevent @TraceID, 125, 34, @on
exec sp_trace_setevent @TraceID, 125, 35, @on

Per Books On Line (http://msdn.microsoft.com/en-us/library/ms186265(SQL.110).aspx), the above statements mean that we are capturing:

• Event ID = 125, i.e. “Deprecation Announcement”
• Column ID = 1, i.e. “TextData”
• Column ID = 3, i.e. “DatabaseID”
• Column ID = 8, i.e. “HostName”
• Column ID = 12, i.e. “SPID”
• and so on..

Setting the filters – sp_trace_setfilter

Now that we have the events & columns for the trace defined, we may want to filter the trace to capture only the events generated by let’s say, a specific host machine or against a specific database. To do so, we can use the sp_trace_setfilter system stored procedure. The stored procedure accepts the following as parameters:

1. @traceid
   • The @trace_id returned by sp_trace_create
2. @columnid
   • The ColumnId for the column that needs to be filtered
   • Use a NULL here to clear all filters for the specified trace
3. @logical_operator
   • Specifies whether the “AND” or “OR” logical operations are applied with respect to other filters on the same trace
4. @comparison_operator
   • Specifies an enumerated integer value that indicates the type of comparison to make
5. @value
   • Specifies the value on which to filter

Words of caution:

1. The data type of @value parameter must match the data type of the column to be filtered
2. To apply two filters on a particular column (e.g. the application name column and one filter on the username column), the filters must be applied on application name in sequence

This is demonstrated very clearly by the following example, which has been borrowed from the Books On Line:

--Notice that filters for column Id #10 ("Application Name") are applied together
sp_trace_setfilter  1, 10, 0, 6, N'SQLT%'
sp_trace_setfilter  1, 10, 0, 6, N'MS%'
sp_trace_setfilter  1, 11, 0, 0, N'joe'

Setting the trace status – sp_trace_setstatus

This one is perhaps the simplest of all system stored procedures for working with a SQL Trace. Now that a new trace created and  events, columns and filters have been defined, it is time to start the trace. The parameters therefore are:

1. @traceid
   • The @trace_id returned by sp_trace_create
2. @status
   • Start or stop the data capture

An example would be:

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

Following these 4 steps essentially completes defining, modifying and starting a SQL Trace. In the next part of the tutorial, we will see how the graphical tool (Profiler) helps us to generate the T-SQL code for a given SQL trace and how to run the T-SQL based SQL Trace.

Further reading:

• sp_trace_create
• sp_trace_setevent
• sp_trace_setfilter
• sp_trace_setstatus
• sp_trace_generateevent

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql