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.
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.
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.
Text value dependent on the event class that is captured in the trace.
Server Process ID assigned by SQL Server to the process associated with the client.
Amount of elapsed time (in milliseconds) taken by the event. This data column is not populated by the Hash Warning event.
Time at which the event started, when available.
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.
Number of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event.
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
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.
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.
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
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.
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.
Friday, March 08, 2013 - 6:52:55 AM - Greg Robidoux
@Greg Robidoux, right, that's what I want. Thanks a lot.
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.
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.
What were those transactions can you please help me out on this.
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
Monday, September 16, 2013 - 2:03:58 PM - Greg Robidoux
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.
Wednesday, September 18, 2013 - 3:57:51 PM - Greg Robidoux
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?
Friday, October 25, 2013 - 9:33:07 AM - Greg Robidoux