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", ) that shows the benefit of a server-side trace over client-side. Among the findings:
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.
I Still Don’t Want to do Server-side Traces. What now?
Keep reading the previous section until you’re sold on the idea.
I'm Sold on Server-side! But how do I start?
Let's walk through the process of creating a server-side trace.
/****************************************************/ /* 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
Sure, it's pretty, but what does it all mean?
Good question. 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.
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!
SQLServerPedia Articles on Profiler
This wiki article was written by Jennifer McCown.
Jen McCown is a SQL Server developer and DBA with over 10 years experience. She writes book and product reviews for ITBookworm.com, makes training videos and blogs for MidnightDBA.com, and maintains both websites.
Her online presence include: