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

sp_trace_setstatus

• 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 @TraceID, 9,  1, @on EXEC sp_trace_setevent @TraceID, 9, 12, @on EXEC sp_trace_setevent @TraceID, 9, 13, @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