Generating Server-Side Traces

Since the publication of my performance-comparison article, “SQL Server Profiler or Server-Side Trace?” August 2008, I’ve received many requests for more information about how to use server-side traces. To satisfy those requests, I’d like to offer a trace-creation overview, and—next time—follow that up with a look at how to process the gathered information.

Because most people seek performance-related information (e.g., trace duration, CPU used, number of reads and writes performed), I’ll focus on capturing such metrics by using the two most commonly used trace events: RPC:Completed and SQL:BatchCompleted (which correspond to the Event Class IDs of 10 and 12, respectively). Together, these two events will capture every request sent to SQL Server from any client. The RPC event will capture all stored procedure calls that the client executes, and the BatchCompleted event will capture everything else. Note that these events don’t capture the individual statements within the procedure or batch, but you can easily capture these items by using other available events.

Beginning the Trace

Your goal is to create a server-side trace that will capture the events you’re interested in and store them in one or more files on a locally attached disk on the server. This tactic ensures a minimal hit on server performance while the trace is active. (All of this article’s commands are fully documented in SQL Server Books Online—BOL—so be sure to read the available documentation to determine exactly how you want to utilize the trace options to suit your specific needs.) To accomplish this goal, you can use a series of T-SQL commands, which I’ve broken into groups for this article. You can download the full sample script at www.sqlmag.com, InstantDoc ID 99940.

1. Create the trace definition, and specify how you want the trace to behave (e.g., the name and location of the resulting file or files). Listing 1’s sp_trace_create command accomplishes this step.

2. Add the events and columns that you want to capture in the trace. All the event and column IDs are listed in BOL under the sp_trace_setevent command, which you see in Listing 2. You need to call this command once for each event/column pair. For the sake of brevity, I haven’t listed all the columns for each event; you can add or delete any that you feel necessary. However, one column that many people tend to forget is the Database ID column, which lets you see which databases you’re accessing.

3. You can set a filter condition to limit which data or how much data you want to capture. Listing 3, which shows the sp_trace_setfilter command, shows a filter of READS >= 5000. Adding such a filter can dramatically reduce the amount of mundane or unwanted data and help you ensure that server performance isn’t affected. But first be sure that you understand what you might be filtering out so that you don’t miss any important or relevant data.

4. To start the trace and begin the data collection, use the sp_trace_setstatus command, which Listing 4 shows. A value of 1 starts the trace, a value of 0 stops the trace (but retains the definition in memory so that you can restart it if necessary), and a value of 2 removes a closed trace definition altogether. Once the trace has begun, you should display the trace ID for future reference. Be aware that trace IDs are reused, so if you close a trace definition and create a new one, you might or might not get the same ID as before.

There are many events and columns to choose from, of course, and I wouldn’t expect anyone to memorize them all. As I mentioned, BOL documents them well. But there’s an easy way to get started. You’re probably already familiar with SQL Server Profiler and know how to choose the events and columns you want to capture. Once you have SQL Server Profiler set the way you want it, simply choose Export from the File menu, as Figure 1 shows. You’ll be able to save a script that has all the proper commands to create a trace just as you defined in SQL Server Profiler. Remember, however, that you’ll need to specify options for the sp_tracecreate command that will tell it exactly how you want the trace to behave, as outlined above.

Now What?

Once you have the trace files, you can view them by loading them into SQL Server Profiler or by using the fn_trace_gettable() function with T-SQL to read them directly. Next time, I’ll explore how to use this function (along with several others) to read, parse, and aggregate the information into something that will help you better understand the metrics you’ve captured.