Microsoft: The Server-side Trace: What, Why, and How

Any time you open SQL Server Profiler and run a trace, you're running a client-side trace. Even if you open SQL Profiler on the server and run it there, it’s still client-side. To run a server-side trace, we need to create a script. If that last sentence made your stomach tighten up, don't worry...this will be completely painless.

 
What's the difference?
There's a cost to running any trace, of course. What we want to do is minimize the impact of the trace on the system, especially for long-running traces or busy production servers.

You can do a lot to reduce the impact of even your client-side traces – for example, filtering your data, limiting the events that you trace on, running short traces, and so on. But there is a significant additional cost to running client-side traces. SQL Server MVP Linchi Shea walks us through a very illuminating benchmark test he conducted ("Performance Impact: Profiler Tracing vs. Server Side SQL Tracing", [1]) that shows the benefit of a server-side trace over client-side. Among the findings:

• While the client-side traces dragged transaction throughput down by 10% (or more), there was little to no difference between NO trace and a server-side trace.
• A Profiler trace can consume a significant amount of network bandwidth (in this test, a minimum of 35%, and sometimes 70% or more of the 100Mbps network). The server-side trace consumes no network bandwidth, of course...it runs on the server!

Some of you savvy DBAs and developers will have already spotted another benefit over client side traces: flexibility. Scripting your traces allows you to automate and customize to your heart's content, and even schedule traces with SQL Agent. Yet another plus is that you can keep your trace defined on the server; if you often have to run a trace for a particular event, like diagnosing a prolonged spike in CPU usage, you can just turn that trace on with a single command.


How do I start?
Let's walk through the process of creating a server-side trace.

1. Open up Profiler and create a new trace.
2. Select Save to File and select a location (it doesn’t matter where, we will be changing this). Select Enable File Rollover.
3. Select Enable Stop Time (again, the actual time doesn’t matter, as we will change this later).
4. Choose your events and columns from the Events Selection tab.
5. Run the trace and then stop it right away.
6. From the File menu, choose Export > Script Trace Definition > For SQL Server 2005 (or whichever is appropriate in your environment) and save the script to file.
7. Open your file in SSMS, making sure you’re connected to the instance you want to profile.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 09/01/2009  10:29:30 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = '2009-09-01 23:28:03.000'
set @maxfilesize = 5

-- 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'InsertFileNameHere', @maxfilesize, @Datetime
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on


What does it all mean?
Let's explore and edit our script.

Variables: @DateTime is the trace stop time. Edit the stop date and time, or set it to NULL for no stop time. Or if you want to impress your friends, set your script up to always give you a set duration trace (in this case, two hours) with @DateTime = DateAdd(h, 2, GetDate())

You can also change the @maxfilesize (which applies to your trace output files) here at the top of your script.

On line 22 you see an exec sp_trace_create statement, which creates but does not start your new trace definition.

• You'll want to change "InsertFileNameHere" to location on the server you’re tracing, or on a drive or share it can reach. For example, "\\mycomputer\sharedFolder\TraceName1". SQL will append the .TRC extension to whatever filename you provide.
• The 0 in your sp_trace_create parameter list represents the @option input. Set @option to 2 for trace file rollover. There’s also an 8 option for blackbox (see BOL) that’s not compatible with the other options, and a 4 option that will shut down SQL Server if your trace fails (I recommend against this).
• If you want to limit the number of files the trace creates - for example, to 10 files - add 10 to the end of the parameter list . This won’t stop the trace after it creates the number of files given for @ filecount. Instead, when the trace creates a new file, it deletes the oldest file for this trace. So if you start off with Trace_1, Trace2 … Trace_10, and then it creates Trace_11, Trace_1 will be deleted. This keeps you from filling up your hard drive with trace files.

So we wind up with something like this:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = @DateTime = DateAdd(h, 2, GetDate())
set @maxfilesize = 25

exec @rc = sp_trace_create
    @TraceID output,
    2,
    N'\\Someplace\MyCoolShare\TraceFile2009',
    @maxfilesize,
    @Datetime,
    20                -- @filecount
if (@rc != 0) goto error

Next up is a big section of sp_trace_setevent statements.

exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
...

These add your selected trace events (such as SQL:Statement Completed and Deadlock Graph) and columns (like TextData) to the trace. Clearly, the easiest way to create this list is to select the events you want in Profiler, before you export the trace script. But it can also be useful to look up which events are represented by which event numbers, so if you want to recreate this trace in the future with more or fewer events, you can just add new events or comment out others. You can find the codes for Profiler events and data columns in Books Online, "Describing Events by Using Data Columns".

If you set any filters, you will see a sp_trace_setfilter command for each just below the sp_trace_setevent section. Here is one example that filters out rows for the trace (@TraceID) where AppName (value 10) is NOT LIKE (value 7) N’SQL Server Profiler...’.

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'

The third parameter (value 0) is the AND operator (the value 1 means OR)...this would come into play if you had other filters. While you certainly can go look up the values you need and set more filters this way, I find it simpler to set your filters in Profiler before you create the trace script.


Can we start the trace now?
Yes, I was just coming to that. The line of code that actually starts your trace is "exec sp_trace_setstatus @TraceID, 1" . I will say that before you start your trace, it’s a good idea to add your stop commands to the bottom of the script. Just like driving a car, you really need to know how to stop before you can go.

 -- sp_trace_setstatus  @traceid =  2,  @status =  0    -- Trace stop
-- sp_trace_setstatus  @traceid =  2,  @status =  2   -- Trace delete
-- SELECT * FROM sys.fn_trace_getinfo(0) ; -- Get info on all server-side traces

Now I have @traceid = 2 in both of these commands, but your trace won't necessarily have an ID of 2. When you run your trace, make note that the returned value is your trace ID. Then change the @traceID in your two sp_trace_setstatus lines to match the traceID returned by your script, and save your script! sp_trace_setstatus...@status=0 stops the trace. Even after you stop the trace, the trace script itself is out on the server. If you choose, you can close and delete the trace from the server with sp_trace_setstatus...@status = 2. You can stop your trace manually, or you can just wait for your stop time to roll around (if you set one). After your trace has stopped, you can go get the trace output files from the directory you specified in the sp_trace_create statement. That’s all there is to it!


Reference:
The Server-side Trace: What, Why, and How
http://www.toadworld.com/platforms/sql-server/w/wiki/10400.the-server-side-trace-what-why-and-how.aspx