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.

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.

Select Enable Stop Time (Auto stop time for trace).Saurabh Sinha Page 1 4/7/2013

4. Choose your events and columns from the Events Selection tab.

5. Run the trace and then stop it right away.

6. From the File menu, choose Export > Script Trace Definition > and save the script to file.

7. Open your file in SSMS, making sure you’re connected to the instance you want to profile.

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

o TraceID - the ID of the trace

o Options - various options that can be set

o TraceFile - physical file name where you want to write the trace file

o MaxFileSize - size of the file, before closing and creating subsequent files

o StopTime - time to stop the trace

• sp_trace_setevent - this procedure specifies what event to capture and what column to capture

o TraceID - the ID of the trace

o EventID - the ID of the event you want to capture

o ColumnID - the ID of the column you want to capture

o 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

o TraceID - the ID of the trace

o ColumnID - the ID of the column you want to set the filter on

o LogicalOperator - specifies whether this is an AND or OR operation

o ComparisonOperator - specify whether the value is equal, greater then, less the, like, etc...

o Value - the value to use for your comparison

• sp_trace_setstatus

o TraceID - the ID of the trace

o 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) |This will give you a list of all of the traces that are running |

| | |on the server. |

| |Select * from sys.traces | |

| |---SQL 2005 onwards | |

|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 |sp_trace_setstatus traceid,0 |To delete you need to stop the trace first and then you can |

|a trace |sp_trace_setstatus traceid, 2 |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 |Table_trace – replace this with the table where you will load the data to |

| |SELECT * INTO Table_trace |traceFileName – use the correct path for the file that you will be reading|

| |FROM ::fn_trace_gettable(‘traceFileName’, DEFAULT) |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 |

| |--Load into an existing table |want to only load one file change the word ‘default’ to a number of files |

| |INSERT INTO Table_trace |you want to load. |

| |SELECT * FROM ::fn_trace_gettable(‘traceFileName’, DEFAU| |

| |LT) | |

| |  | |

|To query the |SELECT * FROM Table_trace |  |

|table | | |

The following table 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. |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download