You could supply a particular trace ID instead of the word default, but if you don't know the trace ID, you can use this form of the statement to see all the traces. Table 3, page 42, contains the output, which shows five rows for five properties, that I received from fn_trace_getinfo() for a trace with a trace ID of 1. The Property 1 row contains the @options parameter value. A trace with a Property 1 value of 1 is most likely a trace started from Profiler. The Property 2 row contains the trace filename, if any. The Property 3 row contains the maximum file size, which in this case defaulted to 5MB, and the Property 4 row contains the stop time, which has no value for my trace. Finally, the Property 5 row shows the trace's status.
To view the trace file's contents and use Profiler to see all the events you've captured, you must remove the trace definition from SQL Server. Stopping the trace isn't enough to let you open the trace file, but you must stop the trace before you remove it. If you try to remove the trace before you stop it, nothing will happen—you won't even get an error message! Listing 3 shows you how to stop and remove a trace with an ID of 1. If you don't know the ID, you can examine the results from the fn_trace_getinfo() function.
Fn_trace_geteventinfo() shows you the events and data columns that a particular trace captures, but the function returns the data with the event and data column IDs instead of a name or explanation, so you must track down their meanings. Fn_trace_getfilterinfo() returns information about a particular trace's filters.
You might have noticed that none of the supplied system procedures let you capture your server-side trace data to a table. This apparent omission is actually a benefit because writing to a table isn't as quick as writing to a file; no matter how busy your server is, having the trace-capture operations not lose any events is essential. Writing to a server-side file uses the same mechanism that SQL Server uses to write to the transaction log, and the trace opens the file only for sequential writing. If you want to capture the data as events occur, table writes would need to happen a row at a time.
If you want to view your trace data in a table, you can copy the data from the trace file to a table after the trace collects the data. Having the trace data in a table lets you use SQL queries to analyze the data. To copy the captured trace data to a table, you can use the table-valued function fn_trace_gettable() in the SELECT INTO statement's FROM clause. This function returns a rowset with one row for each event and a column for every data column that you can trace. You'll probably be interested in only a few columns. Make sure you stop and remove the trace definition before you try to copy the trace data to a table. The example in Listing 4 saves the trace data from the file C:\traceselect.trc to a table called TraceDataTable.
When you store trace data in a table, the EventClass column stores the event type as an integer. The event names that correspond to each integer are in BOL along with the documentation for sp_trace_setevent, but having those event names in a SQL Server table would be useful so that you could do a table lookup. Web Listing 1's table definition and set of INSERT statements let you build a lookup table called EventID_Table, which maps EventClass numbers to event names. (For download instructions, see the More on the Web box.)
Listing 5 shows an example of how to use the EventID_Table and the TraceDataTable to analyze the captured data. The query reports the average duration and number of events for each event type.
Server-side traces are wonderfully useful in all kinds of situations and much more efficient than client-side tracing with Profiler. If you avoid server-side traces because you think writing all the procedure calls and looking up all the necessary event and data column ID information is too hard, take heart. You can use Profiler to define all the events, data columns, and filters that you want, then use the Script Trace option on the File menu to produce the calls to the stored procedures that correspond to the trace elements you select. The only change you need to make is to provide a filename to hold the captured data. Then, you can run the newly created script through Query Analyzer to run your server-side trace.
Performance Considerations
SQL Server tracing incurs no overhead unless it captures an event, and most events need very few resources. Profiler becomes expensive only if you trace all the more than 100 event classes and capture all the data from those events. My preliminary testing shows a maximum of 5 percent to 10 percent overhead if you capture everything. Most of the performance hit is because of a longer code path; the actual resources that the trace needs to capture event data aren't particularly CPU-intensive. In addition, to minimize the performance hit, you can define all your traces as server-side traces and avoid the overhead of producing rowsets to send to the Profiler client.
End of Article
Prev. page
1
2
[3]
next page -->
