What is the difference between SQL Profiler vs Server side Trace


Server Side Trace vs Client Side Trace


When troubleshooting a SQL Server problem, one of the tools comes in mind is Profiler. This tool allows us to collect statements that run on our SQL Server for troubleshooting.

Download Document
You might also like to read : How to read default trace in sql server  

Uses of SQL Profiler:

  1. Its major use is by applications to troubleshoot them
  2. In Performance issues it is hardly used as it eat lot of resources and trouble your server which is already hit by performance
  3. It could be used to see performance of all stored procedures just after a migration (you must have metrics of before migration)
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 could hit you hard if you are running profiler for hours.  This usually happens right before the problem your trying to troubleshoot occurs and you don't end up collecting that valuable information you need.

Client side trace:

1. Any time you open SQL Server Profiler and run a trace, you're running a client-side trace.
2. If you open SQL Profiler on the server and run it there, it’s still client-side.

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.

 To run a server-side trace, one needs to create a script using below steps.

  1. Open up Profiler and create a new trace as per your requirement.
  2. Select Save to File and select a location to save trace file.
  3. Select Enable File Rollover.
  4. Select Enable Stop Time (Auto stop time for trace).
  5. Choose your events and columns from the Events Selection tab.
  6. Run the trace and then stop it right away.
  7. From the File menu, choose Export > Script Trace Definition > and save the script to file.
  8. Open your file in SSMS, making sure you’re connected to the instance you want to profile.
  9. Search for sp_trace_create  and replace fileName with exact location where you want to save your trace ; Replace 0 with 2 to enable file rollover

Example:

 /******************************************************/
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime

Syntax: Reference

sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value ---> Set it 2 for file rollover
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]
 /******************************************************/
The server side trace can be modified to collect any event that the trace process can capture what data to collect

To create the trace for these events and columns the command would look as follows: this generated as sql script from above mention steps

/***********************************************/
/* Server Side Trace                           */
/***********************************************/
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 04/07/2013  00:24:13 AM         */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
 
set @DateTime = '2013-04-07 03:23:00.000'
set @maxfilesize = 5
 
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
 
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime
if (@rc != 0) goto error
 
-- Please modify 2nd column of sp_trace_create with 2 from 0 to enable file rollover
 
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
 
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d3b8ff1d-57c8-400f-81b7-d193ab7c4ce8'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
 
-- display trace id for future references
select TraceID=@TraceID
goto finish
 
error:
select ErrorCode=@rc
 
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
    • TraceID - the ID of the trace
    • Status - stop, start or close a trace

To add additional events and columns we would just include additional sp_trace_setevent commands such as the following to collect event

EXEC sp_trace_setevent @TraceID9,  1@on
EXEC sp_trace_setevent @TraceID912@on
EXEC sp_trace_setevent @TraceID913@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)
 
Select * from sys.traces
 ---SQL 2005 onwards
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 view in no. of ways

1.     Just drag and drop them in sql profiler
2.     Use fn_trace_gettable  with below query
 /******************************************************/
SELECT DB_NAME (DatabaseID) ,* FROM
fn_trace_gettable( convert (varchar(1000),
'location of file\tracefile.trc')  , default)
order by starttime
 /******************************************************/
3.     Load the data into a trace table and then run queries against the trace file.  Using below commands .

Task
Command
Notes
To load a trace
--Load into a new table
SELECT INTO Table_trace
FROM ::fn_trace_gettable('traceFileName'DEFAULT)


--Load into an existing table
INSERT INTO Table_trace
SELECT FROM ::fn_trace_gettable('traceFileName'DEFAULT)
 
  • Table_trace – 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 Table_trace
 

 

 How this is possible that you run trace and every thing is sucessfull in first shot , so below are errors you might face among these 12 and 14 are pretty common
 
The following describes the code values that users may get following completion of the stored procedure.

Return code
Description
0
No error.
1
Unknown error.
2
The trace is currently running. Changing the trace at this time will result in an error.
3
The specified event is not valid. The event may not exist or it is not an appropriate one for the store procedure.
4
The specified column is not valid.
9
The specified trace handle is not valid.
11
The specified column is used internally and cannot be removed.
12
Could not create a trace file.(The system cannot find the path specified or file already exists)
13
Out of memory. Returned when there is not enough memory to perform the specified action.
14
The requested trace stop time has been already passed.
16
The function is not valid for this trace.

You might also like to read : How to read default trace in sql server  

 

4 comments:

  1. Great Post. Don't you think server side trace would effect the server performance, of OLTP application. instead,backup the prod database to the test db on the same server and start using profiles along with the tunning advisor if needed. your views??

    ReplyDelete
    Replies
    1. Hi Sapan

      Backing up prod db and restore on on test database can be a good approach , but there is one glitch in that. you have to generate same load on test env and getting test env ready can be time consuming.

      Regarding server side trace: yes it takes resources but its not as heavy as running profiler.

      Delete
  2. This article is fantastic - thank you! Just saved my sanity

    ReplyDelete