solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL compliance manager

SQL safe backup

SQL Sentry Performance Advisor for SQL Server

SQL diagnostic manager

SQL Monitor

SQL Server Performance Statistics Using a Server Side Trace

By: Greg Robidoux | Read Comments | Print

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

Related Tips: More

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
45	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:

/***********************************************/ 
/* Server Side Trace                           */ 
/***********************************************/ 
-- Declare variables 
DECLARE @rc INT 
DECLARE @TraceID INT 
DECLARE @maxFileSize bigint 
DECLARE @fileName NVARCHAR(128) 
DECLARE @on bit 

-- Set values 
SET @maxFileSize = 5 
SET @fileName = N'C:\TestTrace' 
SET @on = 1 

-- Create trace 
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL  

-- If error end process 
IF (@rc != 0) GOTO error 

-- Set the events and data to collect 
EXEC sp_trace_setevent @TraceID, 45,  1, @on 
EXEC sp_trace_setevent @TraceID, 45, 12, @on 
EXEC sp_trace_setevent @TraceID, 45, 13, @on 
EXEC sp_trace_setevent @TraceID, 45, 14, @on 
EXEC sp_trace_setevent @TraceID, 45, 15, @on 
EXEC sp_trace_setevent @TraceID, 45, 16, @on 
EXEC sp_trace_setevent @TraceID, 45, 17, @on 

-- Set Filters 
-- filter1 include databaseId = 6 
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6 
-- filter2 exclude application SQL Profiler 
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' 

-- Start the trace 
EXEC sp_trace_setstatus @TraceID, 1 
  
-- display trace id for future references  
SELECT TraceID=@TraceID  
GOTO finish  

-- error trap 
error:  
SELECT ErrorCode=@rc  

-- exit 
finish:  
GO

There are basically four components to this to get this running:

sp_trace_create - this procedure creates the trace and has 5 parameters
- TraceID - the ID of the trace
- Options - various options that can be set
- TraceFile - physical file name where you want to write the trace file
- MaxFileSize - size of the file, before closing and creating subsequent files
- StopTime - time to stop the trace
sp_trace_setevent - this procedure specifies what event to capture and what column to capture
- TraceID - the ID of the trace
- EventID - the ID of the event you want to capture
- ColumnID - the ID of the column you want to capture
- On - whether you want to turn this event on or off
sp_trace_setfilter - this procedure specifies the filters to set. This determines whether you include or exclude data
- TraceID - the ID of the trace
- ColumnID - the ID of the column you want to set the filter on
- LogicalOperator - specifies whether this is an AND or OR operation
- ComparisonOperator - specify whether the value is equal, greater then, less the, like, etc...
- Value - the value to use for your comparison
sp_trace_setstatus
- TraceID - the ID of the trace
- Status - stop, start or close a trace

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.

EXEC sp_trace_setevent @TraceID, 10,  1, @on 
EXEC sp_trace_setevent @TraceID, 10, 12, @on 
EXEC sp_trace_setevent @TraceID, 10, 13, @on 
EXEC sp_trace_setevent @TraceID, 10, 14, @on 
EXEC sp_trace_setevent @TraceID, 10, 15, @on 
EXEC sp_trace_setevent @TraceID, 10, 16, @on 
EXEC sp_trace_setevent @TraceID, 10, 17, @on

To start, stop and delete a trace you use the following commands.

Task	Command	Notes
To find traceid	SELECT * FROM :: fn_trace_getinfo(default)	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,0 sp_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.

Related Tips: More | Become a paid author

Last Update: 8/8/2006

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured."

Write, edit, and explore SQL effortlessly with SQL Prompt.

What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995.

Get SQL Server Tips Straight from Kevin Kline.

Join the over million SQL Server Professionals who get their issues resolved daily.

Are you waiting on SQL Server? Learn about these DMV's.