SQL Server Performance Statistics Using a Server Side Trace

By:   |   Comments (19)   |   Related: > 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 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, 41,  1, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, June 17, 2021 - 5:44:52 PM - Kamalpreet Singh Back To Top (88871)
Can we run trace on Analysis Services and save its output in SQL Server database table?

Thursday, April 20, 2017 - 11:18:48 AM - Greg Robidoux Back To Top (55063)

Hi Brenda,

This is very helpful to capture what is occuring in your enviornment. This way you can find long running queries to further investigate.

There is some overhead, that is why it is best to minimize how many events you collect.  You can also filter down to a particular login or workstation to limit that amount of data that is collected.  I also like to start with durations of 500ms or longer or reads of 5000 or more.  This further limits the amount of data that is collected.

By default, there is a trace that runs all of the time to collect information from SQL Server, so this is safe to do in a production enviornment.

-Greg

Thursday, April 20, 2017 - 11:12:19 AM - brenda Back To Top (55062)

How does this affect the performance of the process that is being traced? Is it safe to do this in production against a time critical task? 

 

Monday, July 11, 2016 - 11:15:13 AM - Greg Robidoux Back To Top (41862)

Hi B,

this should work with all versions of SQL Server.  What version of SQL Server are you using?

-Greg

Monday, July 11, 2016 - 11:04:40 AM - B Back To Top (41861)

 What versions of SQL Server does this apply to?

 

Friday, March 18, 2016 - 10:54:46 AM - Greg Robidoux Back To Top (40988)

Hi Siddu,

Asimple way of doing this is to load the data into a central table in a work database on your server and then have a process that queries the data and places it into the correct database.  This way you can use T-SQL to write the queries and have more control of what data goes where.

-Greg

Friday, March 18, 2016 - 9:34:02 AM - siddu Back To Top (40984)

Hallo Greg, 

i am working with server side trace as explain above

is thr any possibility to insert this info to that particular log_database of production database.

 

e.g : production database                      log_database

        Test1_db                                                 Test1_db_log

        Test2_db                                                  Test2_db_log

         Test3_db                                                 Test3_db_log

         Test4_db                                                 Test4_db_log

         Test5_db                                                 Test5_db_log

       and so on up to 

     Test30_db                                                         Test30_db_log

 

    now trace capture all database related blocking information and we can see in text data column

for example:

 

if blocking occured in 'Test1_db' then we need to store in 'Test1_db_log'  table1

if blocking occured in 'Test2_db' then we need to store in 'Test2_db_log'  table1

if blocking occured in 'Test3_db' then we need to store in 'Test2_db_log'  table1

if blocking occured in 'Test4_db' then we need to store in 'Test4_db_log'  table1

if blocking occured in 'Test5_db' then we need to store in 'Test5_db_log'  table1

 

and so on up to 

if blocking occured in 'Test30_db' then we need to store in 'Test30_db_log'  table1

here table is same in all databases.

  now how to insert this information into different databases  in a table as above described

through job we can insert in single database bcz while creating job we need to select database name in job step.

 

could you please help me on this

Thanks in Advance.

siddu.

 

Friday, October 25, 2013 - 9:33:07 AM - Greg Robidoux Back To Top (27280)

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.

Friday, October 25, 2013 - 3:46:47 AM - Laszlo Tordai Back To Top (27276)

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

Wednesday, September 18, 2013 - 3:57:51 PM - Greg Robidoux Back To Top (26847)

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

Wednesday, September 18, 2013 - 3:19:34 PM - Ramesh Back To Top (26846)

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.

Monday, September 16, 2013 - 2:03:58 PM - Greg Robidoux Back To Top (26819)

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

Monday, September 16, 2013 - 6:26:18 AM - Varun Back To Top (26807)
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

Saturday, March 16, 2013 - 8:58:55 AM - kranthi Back To Top (22828)

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.

 

Friday, March 8, 2013 - 7:18:42 PM - unruledboy Back To Top (22676)

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

Friday, March 8, 2013 - 6:52:55 AM - Greg Robidoux Back To Top (22659)

@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

Thursday, March 7, 2013 - 11:22:23 PM - unruledboy Back To Top (22644)

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.

Monday, November 26, 2012 - 10:29:09 AM - Greg Robidoux Back To Top (20537)

@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

Monday, November 26, 2012 - 10:08:10 AM - Verjik Back To Top (20536)

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














get free sql tips
agree to terms