Running a Server Side Trace

SQL Server Profiler can bring your production server to a screeching halt

In a prior post entitled Scripting Server Side Traces I lauded the laurels of SQL Server Profiler as a wonderful tool to use in development and QA environments to really see what’s happening inside your SQL Server. It’s a great way to shine a spotlight into the nooks and crannies of your database server. But try to use it on a production server and you’re likely to notice some unpleasant performance degradations. My warning to you was to use with caution on production servers. It’s better to use Server Side Traces and I demonstrated how SQL Server Profiler can be used to script out the Server Side Traces for you. If you haven’t already read that blog post, it may be worth while to go back and read it before continuing on with this one.

In this, the second of a three part series on Server Side Traces, we’ll examine the trace script to see what it’s doing. Then I’ll show how you can start and stop Server Side Traces using T-SQL procedures.

Modifying the script

We’ll start with a relatively simple Server Side Trace script that was generated for us by SQL Server Profiler. The script can be seen below. It’s rather long, but we’ll break it down into just a few sections that can be easily digested.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 08/27/2009  08:59:12 AM         */
/****************************************************/

–- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

–- Please replace the text InsertFileNameHere, with an appropriate
–- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
–- will be appended to the filename automatically. If you are writing from
–- remote server to local drive, please use UNC path and make sure server has
–- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL
if (@rc != 0) goto error

–- Client side File and Table cannot be scripted

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @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, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

–- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

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

–- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Defining variables

The script starts off declaring some variables. You’ll want to make sure that the values you configured in SQL Server Profiler were exported correctly. In particular, make sure the @maxfilesize variable is set to the number of megabytes that you’d like for capture. In the example below, I’ve configured the trace to capture 500 MB of data.

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

Creating the trace

The next part of the script creates the trace using the sp_trace_create stored procedure. Here, as the comments indicate, you’ll want to provide the fully qualified path for the output file that you’ll use to store the captured data. In this example, I’m collecting it in a E:\demo\demotrace2.trc. Notice that the trc extension is automatically added for us; you don’t need to include it. If you do, you’ll end up with a file named demotrace2.trc.trc. That’s redundantly redundant.

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL

The sp_trace_create procedure also accepts some other parameters that are not automatically produced when you export from SQL Server Profiler. For example, you can specify the TRACE_FILE_ROLLOVER option and the @filecount parameter to allow the trace information to span multiple operating system files. Modifying the preceding stored procedure call as shown below would allow the trace to rollover to subsequent files (the second parameter is 2), up to a maximum of five files (the last parameter is 5).

exec @rc = sp_trace_create @TraceID output, 2, N’E:\demo\demotrace2′, @maxfilesize, NULL, 5

You can also specify a time for the trace to end. This is the second to last parameter, which is NULL in the example above.

Notice that the @TraceId parameter is defined as an output parameter. When the sp_trace_create procedure is called, it creates the new trace and returns to us a unique number that we can use to refer to the new trace. We’ll use the @TraceId variable throughout the rest of the script to update the trace properties.

For more information, visit Books Online sp_trace_create.

Specifying the events

The next section of the script defines the events that the trace will capture. The sp_trace_setevent stored procedure is executed repeatedly to define each and every event we’ve asked to see. If you’re monitoring a lot of events, this section is rather tedious to look at. For more information, see sp_trace_setevent in Books Online.

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
…

Filtering the events

Filters are set using the sp_trace_setfilter stored procedure in the next section. This is where we can narrow down our trace to just one database or to queries that take longer than some threshold we’ve set. On a SQL Server instance with lots of databases, you’ll definitely want to set some filters. For more information on filtering traces, visit sp_trace_setfilter in Books Online.

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

Starting and stopping the trace

Once the trace has been fully defined within SQL Server, all we have left to do is to start the trace. We do that by setting its status using the sp_trace_setstatus stored procedure.

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

There are two parameters that we must supply to the sp_trace_setstatus stored procedure. The first is the trace that we wish to modify; this was the output of the sp_trace_create procedure at the beginning of the script. The second is that status of the trace.

A value of 0 for the status stops the trace. 1 starts the trace. 2 gets rid of the trace from the server. See Books Online for information about the sp_trace_setstatus stored procedure.

Next up: scheduling Server Side Traces

Alright! Now that we have a pretty good understanding of what this script does and how to manually start and stop Server Side Traces, next time we’ll look at ways to automate the trace.  Scheduling the trace will make it easy for us automatically collect data as  at various points throughout the business day and week.  This information will prove invaluable in helping us to benchmark our systems.

Until next time,

Joe