Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Performance Statistics Using a Server Side Trace

MSSQLTips author Greg Robidoux By:   |   Read Comments (12)   |   Related Tips: More > Profiler and 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:

/***********************************************/
/* 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 output0@fileName@maxFileSize, NULL 

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

-- Set the events and data to collect
EXEC sp_trace_setevent @TraceID41,  1@on
EXEC sp_trace_setevent @TraceID4112@on
EXEC sp_trace_setevent @TraceID4113@on
EXEC sp_trace_setevent @TraceID4114@on
EXEC sp_trace_setevent @TraceID4115@on
EXEC sp_trace_setevent @TraceID4116@on
EXEC sp_trace_setevent @TraceID4117@on

-- Set Filters
-- filter1 include databaseId = 6
EXEC sp_trace_setfilter @TraceID3106
-- filter2 exclude application SQL Profiler
EXEC sp_trace_setfilter @TraceID1007N'SQL Profiler'

-- Start the trace
EXEC sp_trace_setstatus @TraceID1
 
-- 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 @TraceID10,  1@on
EXEC sp_trace_setevent @TraceID1012@on
EXEC sp_trace_setevent @TraceID1013@on
EXEC sp_trace_setevent @TraceID1014@on
EXEC sp_trace_setevent @TraceID1015@on
EXEC sp_trace_setevent @TraceID1016@on
EXEC sp_trace_setevent @TraceID1017@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.


Last Update: 11/26/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, November 26, 2012 - 10:08:10 AM - Verjik Read The Tip

Hello,

 

45

SP:StmtCompleted

Indicates that a Transact-SQL statement within a stored procedure has finished executing.

41

SQL:StmtCompleted

Occurs when the Transact-SQL statement has completed.

Looks like 45 and 41 are mixed above.

 

http://msdn.microsoft.com/en-us/library/ms186265.aspx


Monday, November 26, 2012 - 10:29:09 AM - Greg Robidoux Read The Tip

@Verjik - thanks for pointing this out. 

This tip was originally written for SQL Server 2000.  The values changed in SQL Server 2005. 

I have updated the tip to reflect the current values.  Thanks


Thursday, March 07, 2013 - 11:22:23 PM - unruledboy Read The Tip

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 Read The Tip

@unruledboy - take a look at this blog post

http://sqlblog.com/blogs/kalen_delaney/archive/2007/06/29/did-you-know-a-6th-parameter-when-creating-a-trace.aspx


Friday, March 08, 2013 - 7:18:42 PM - unruledboy Read The Tip

@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.


Saturday, March 16, 2013 - 8:58:55 AM - kranthi Read The Tip

Hey Greg, 

I have a question,

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.

 


Monday, September 16, 2013 - 6:26:18 AM - Varun Read The Tip
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

Monday, September 16, 2013 - 2:03:58 PM - Greg Robidoux Read The Tip

@Varum - no you can add a new item to the trace and then restart it.  So stop it, add new items and start again.


Wednesday, September 18, 2013 - 3:19:34 PM - Ramesh Read The Tip

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 Read The Tip

@Ramesh - there is not a way to remove all traces with one command.  You would need to do this for each trace.


Friday, October 25, 2013 - 3:46:47 AM - Laszlo Tordai Read The Tip

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?

Thanks
  laszlo


Friday, October 25, 2013 - 9:33:07 AM - Greg Robidoux Read The Tip

Hi Laszlo, it is not possible to limit the amount of TextData that is captured from a trace.

You could setup a rolling trace and then load the data into SQL Server.  Then when you get the data in SQL Server you can trim the TextData column.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.