SQL Server Performance Statistics Using a Server Side Trace

Problem

When troubleshooting a SQL Server performance problem, one of the tools to use is Profiler. This tool allows you to collect metrics on statements that are run on your SQL Server for analysis and troubleshooting. The problem with using Profiler is that it is a client tool and unless it is run on the server itself the connection may be lost and your trace stops. This usually happens right before the problem your trying to troubleshoot occurs and you don't end up collecting that valuable information you need.

Solution

One alternative to using Profiler is to run a Server Side Trace. This process runs on the server and collects trace statistics pretty much the same way that you do using Profiler, but the process involves running a T-SQL script to start, run and stop the trace vs. using the Profiler GUI.

The server side trace can be modified to collect any event that the trace process can capture, but for this example we are just looking at SQL:StmtCompleted events which occur when a T-SQL statement has completed. For a complete list of events click here.

EventNumber

Event

Description

41

SQL:StmtCompleted

Occurs when the Transact-SQL statement has completed.

In addition to collecting information on certain events, you can also specify what data to collect. In this example we are collecting the statements or TextData, the SPID, Duration etc... For a complete list of columns click here.

ColumnNumber

Column

Description

1

TextData

Text value dependent on the event class that is captured in the trace.

12

SPID

Server Process ID assigned by SQL Server to the process associated with the client.

13

Duration

Amount of elapsed time (in milliseconds) taken by the event. This data column is not populated by the Hash Warning event.

14

StartTime

Time at which the event started, when available.

15

EndTime

Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. It is also not populated by the Hash Warning event.

16

Reads

Number of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event.

17

Writes

Number of physical disk writes performed by the server on behalf of the event.

To create the trace for these events and columns the command would look as follows:

To add additional events and columns you would just include additional sp_trace_setevent commands such as the following to collect event 10 RPC:Completed for the same columns that we were collecting above.

This will give you a list of all of the traces that are running on the server.

To start a trace

sp_trace_setstatus traceid, 1

TraceId would be the value of the trace

To stop a trace

sp_trace_setstatus traceid, 0

TraceId would be the value of the trace

To close and delete a trace

sp_trace_setstatus traceid,0sp_trace_setstatus traceid, 2

To delete you need to stop the trace first and then you can delete the trace. This will close out the trace file that is written.

Once the data has been collected you can load the data into a trace table and then run queries against the trace file. Following are some commands that can be used to load the trace data into a trace table.

Task

Command

Notes

To load a trace

--Load into a new table SELECT * INTO sqlTableToLoad FROM ::fn_trace_gettable('traceFileName', DEFAULT)

--Load into an existing table INSERT INTO sqlTableToLoad SELECT * FROM ::fn_trace_gettable('traceFileName', DEFAULT)

sqlTableToLoad - replace this with the table where you will load the data to

traceFileName - use the correct path for the file that you will be reading the data from. If you are on the server use the UNC path.

default - if this is set to default the load will load the file you specified as well as all additional sequenced files that exist. If you want to only load one file change the word 'default' to a number of files you want to load.

To query the table

SELECT * FROM sqlTableToLoad

Next Steps

Using Profiler to trace events that are occurring is a must when troubleshooting performance issues

Learning how to use Server Side Traces can enhance your performance monitoring process. You can set these up and turn them on and off as needed.

Add this handy process to your SQL Server toolkit and use all the tools available to maintain your servers.

Last Update: 11/26/2012

About the author

Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

In some cases we have extremely long sql statements, like passing huge xml stream to a stored procedure. Is it possible to save in the trace say the first 200 characters of TextData only. Can that be achieved by the Profiler GUI?

How do I undo all the traces that I set. Is there is single command to reset the traces. I have several traces that I enabled as part of my auditing requirement. However once it is set, I want to reset it back to no traces.

Hi Greg,
I have a Question.
I have run a trace on production server as I want to collect information about the transactions.
When i try to add another database to the monitor the alert pop ups to stop the trace first.
Do I need to delete the trace in order to add another database or to modify the trace

I have run a sql trace on a production server as I want to collect information about transactions running on particular database.

I have used a filter option to capture all the transactions that ran within the particular database.

After the trace got completed by using a script, I filtered the transactions which ran greater than 5 secs than I have seen some 60 records as output of the trace but the thing is database column was null and text data column was null.

BTW, when I try to add another database to monitor, it asks me to stop the trace first, then I use "sp_trace_setstatus traceid, 0" to stop the trace, then run "EXEC sp_trace_setfilter 2, 3, 1, 0, 5" to trace database id 5. It tells me same event column id should be put into one group. I googled and checked the BOL, could not figure it. Finally what I did is use "sp_trace_setstatus traceid, 2" to delete the trace and run the whole script again to create the trace, which is quite annoying.

how to do rollover purging for the trace data? for example, due to limited disk space, I only want the trace to keep 1GB data, any new data tries to write after 1GB, oldest trace data records should be removed.