See Also: Main_Page - Monitoring & Tuning

As you might guess the Profiler functionality is implemented by executing system stored procedures. Therefore, you do not need to have the Profiler's graphical user interface to setup and manage traces - you can do the same through Transact SQL commands. New traces can be created by executing the system procedure sp_trace_create.

The first parameter, @traceid, is an internal trace identifier assigned by SQL Server to each trace. You cannot override the identifier, but you can use it to further manipulate the trace by adding events and data columns, and by setting filters. The second parameter, @options, shows the options selected for the new trace. This parameter can take one of the values shown in the table below:

Option Option Value Explanation
TRACE_PRODUCE_ROWSET 1 Specifies that the trace will produce a rowset
TRACE_FILE_ROLLOVER 2 When the trace file reaches its maximum size SQL Server will automatically create a new file and continue recording trace events there. The new trace files are appended numeric suffixes such as trace_file_1.trc, trace_file_2.trc, etc. If the maximum file size isn't specified the default is 5mb.
SHUTDOWN_ON_ERROR 4 This option forces shutting the server down if the trace cannot write to the file. This is useful for performing a security audit.
TRACE_PRODUCE_BLACKBOX 8 Specifies that a record of the last 5 MB of trace information produced by the server will be saved by the server. TRACE_PRODUCE_BLACKBOX is not compatible with any other options.

You can specify the sum of two option values (as long as they're compatible with each other) if you wish to turn two of them on simultaneously for a new trace.

The @tracefile parameter specifies a full path and file name for the trace file. Traces can be saved to a local directory or on a network drive. If you wish to specify a network drive as the destination, you must supply the UNC path as in "\\server_name\sharename\directory\file_name". SQL Server will append .trc extension to all trace files.

The @stoptime parameter specifies a date and time in the future when the trace recording should be stopped. If you don't supply a value for this parameter, the trace will continue executing until it is stopped manually or until the server shuts down.

When you create a new trace with sp_trace_create the new trace will not be started - it will remain in the stopped mode until it is explicitly started.

For example, the following command creates a trace saved to a local file with a default maximum file size and no explicit stop time:

/* variable to hold a trace identifier */  
DECLARE @trace_id INT    
/* create a trace */  
EXEC sp_trace_create     @traceid = @trace_id  OUTPUT,    @options =  2,   @tracefile =  N'e:\trace2'     
/* get the trace identifier for future use */  
SELECT @trace_id AS trace_id

Results:

trace_id  4

Now that we have a trace, we can add events and data columns to it. This can be accomplished by executing the system procedure sp_trace_setevent. The same procedure can be used to remove events and data columns that are no longer needed.

Note: you can only add events to a trace that is stopped (has a status of 0).

The first parameter is the trace identifier. The event ID and column ID refer to event number and data column number. The last parameter, @on, is a BIT value, either 1 (on) or 0 (off).

The last parameter of this procedure, @on, works according to the value of @column_ID parameter. The rules are as follows:

  1. If @on = 1 and @column_id is NULL then all columns are cleared and the event is turned on
     
  2. If @on = 1 and @column_id is NOT NULL then the event is turned on for the specified column
     
  3. If @on = 0 and @column_id is NULL then all columns are cleared and the event is turned off
     
  4. If @on = 0 and @column_id is NOT NULL then the column is turned off for that event

To add a few events and columns to the trace created in the previous example, execute the following commands:

DECLARE @on BIT, @tid INT  
SELECT @on=1, @tid = 4    
/* add text data column for SQL: BatchCompleted event */  
EXEC sp_trace_setevent @traceid = @tid,   @eventid = 12, @columnid = 1, @on = @on    
GO    
DECLARE @on BIT, @tid INT  SELECT @on=1, @tid = 4    
/* add text data column for RPC: completed event */  
EXEC sp_trace_setevent @traceid = @tid,   @eventid = 10, @columnid = 1, @on = @on    
GO    
DECLARE @on BIT, @tid INT  SELECT @on=1, @tid = 4    
/* add application name column for SQL: Batch Completed event */  
EXEC sp_trace_setevent @traceid = @tid,   @eventid = 12, @columnid = 10, @on = @on

You can also define filters for a trace using the system procedure sp_trace_setfilter. Note: you can set filters on a trace only while it is stopped (has a status of 0).

Much like when working with the Profiler, trace filters can be set on data columns. The second parameter of sp_trace_setfilter is the column number. The @logical_operator parameter specifies whether AND or OR operators will be applied. @logical_operator designates a value of 1 for OR and the value of 0 for AND.

The comparison operator can have several different values, as shown in the following table:

Comparison Operator Value Explanation
0 Equal
1 Not Equal
2 Greater than
3 Less than
4 Greater than or equal
5 Less than or equal
6 LIKE
7 NOT LIKE

Recall that not all data columns allow the same criteria. Please refer to the table of data columns to find out which criteria are supported by each.

The @value parameter is the value to filter on. The data type of this parameter must match the data type of the column. When the comparison operator is LIKE or NOT LIKE the @value parameter can include wildcard symbols, such as "%".

To continue with the previous examples, you could set a filter excluding the Profiler commands from the trace as follows:

DECLARE @tid INT  
SELECT @tid = 4    
/* turn off tracing of application name N'SQL Profiler' */  
EXEC sp_trace_setfilter @traceid = @tid,   @columnid = 10, @logical_operator = 1,   @comparison_operator = 7, @value = N'SQL Profiler'
Next you can use the system procedure sp_trace_setstatus to modify the trace status - start, stop or delete the trace. The first parameter is the trace identifier. The second parameter can take the following values:
  • 0 = stop the trace
  • 1 = start the trace
  • 2 = close the trace and delete its definition

You must stop the trace before you can delete its definition.

To start a trace defined in the previous example you would execute the following command:

sp_trace_setstatus 4, 1

When the trace is no longer needed, we can close it and delete it by executing the following commands:

sp_trace_setstatus 4, 0  GO  Sp_trace_setstatus 4, 2

In addition to system stored procedures, SQL Server also includes a few built-in user-defined functions that can be used for querying the existing trace and its definition. You can execute built-in user-defined functions by specifying the column list or "*" in the SELECT statement and prefixing the UDF with two colons, as follows:

SELECT column_list FROM :: FN_Buit_in_UDF()

The Fn_Trace_Getinfo function returns information about a specific trace or all traces defined on the current instance of SQL Server. This function accepts the trace identifier as an optional parameter. To get information on all existing traces you can supply the keyword "DEFAULT" or 0 as the parameter, as follows:

SELECT * FROM :: fn_trace_getinfo(DEFAULT)

This function returns the trace identifier, property and the value of a property in the result set. Trace properties returned will be the following:

  • 1 - Trace options chosen when the trace was created with sp_trace_create procedure
  • 2 - File name
  • 3 - Maximum size of the trace file
  • 4 - Stop time if specified with sp_trace_create
  • 5 - Current trace status

The sample output of fn_trace_getinfo is shown below:

traceid  property  Value  1        1         2  1        2         e:\new_trace  1        3         5  1        4         NULL  1        5         0

The Fn_Trace_Getfilterinfo function returns information about the filters defined on a particular trace. You must pass the trace identifier to this function. For example, the following command returns filters defined for the trace created earlier:

SELECT * FROM :: fn_trace_getfilterinfo(4)

Results:

columnid  logical_operator  comparison_operator  Value  
10        0                 7                    SQL Profiler

The Fn_Trace_Geteventinfo function gets the events defined for a particular trace. You must pass the trace identifier to this function. For example:

SELECT * FROM :: fn_trace_geteventinfo(4)

Results:

eventid  columnid  
10       1  
10       12  
12       1  
12       10  
12       12

The Fn_Trace_Gettable function can be used to read the contents of the trace. Note that you must close the trace by setting its status to zero before you'll be able to read its contents. This function accepts two parameters. The first parameter specifies the initial trace file name. The second parameter is the number of rollover files to be read. You may specify the DEFAULT keyword for the second parameter to read all rollover files associated with this trace.

Note that when reading trace contents with fn_trace_gettable you get all possible data columns in the output, regardless of how many of them have been turned on. Columns that haven't been turned on will contain NULL values. To make the output of the function easier to read, you might wish to specify the column names you wish to see, as in the following example:

SELECT TextData,    ApplicationName,    SPID,    ServerName,    EventClass   FROM :: fn_trace_gettable('e:\trace2.trc', DEFAULT)

Results (abbreviated):

TextData                                        ApplicationName          SPID  ServerName       Event Class
sp_trace_setstatus 1, 1                         SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT N'Testing Connection...'                 SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters  SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
SELECT * FROM sysobjects                        SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT N'Testing Connection...'                 SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters  SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
SELECT N'Testing Connection...'                 SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters  SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
SELECT * FROM :: FN_TRACE_GETINFO(1)            SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT * FROM :: FN_TRACE_GETFILTERINFO(1)      SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT * FROM :: FN_TRACE_GETEVENTINFO(1)       SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT N'Testing Connection...'                 SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters  SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12  
SELECT * FROM :: FN_TRACE_GETTABLE              SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT * FROM :: FN_TRACE_GETTABLE(1)           SQL Query Analyzer       54    BP-5CHSFFH2HEJ1  12  
SELECT N'Testing Connection...'                 SQLAgent - Alert Engine  52    BP-5CHSFFH2HEJ1  12
Trace file setup through Transact-SQL can also be viewed through the Profiler. Simply choose File --> Open --> Trace File and then navigate to the trace file's location.