SQL Tracing Internals

This post has been republished via RSS; it originally appeared at: Configuration Manager Archive articles.

First published on TECHNET on Jun 12, 2014
https://blogs.technet.microsoft.com/umairkhan/2014/06/12/sql-tracing-internals/

Hi Folks,

We all might be using the GUI way of taking and analyzing a SQL profiler trace and have found it difficult to analyze it (Atleast I have :)). Also given the huge data, it become a daunting task with filtering or UI crashing or being slow. I got to know about this scripted way quite a while ago and found it cool and easy and make the job very simple for us. :)

There is a ErrorTrace.sql file attached in this post which contains the trace events and columns that we will be capturing (contains all the general events that we choose from the GUI list). In addition to collecting information on certain events, you can also specify what data to collect. Example:TextData, the SPID, Duration etc... For a complete list of columns and events follow: http://msdn.microsoft.com/en-us/library/ms186265.aspx .

Syntax:

sp_trace_setevent [ @traceid = ] trace_id

 , [ @eventid= ]

event_id

 , [ @columnid= ]

column_id

 , [ @on= ]

on

Eg:

exec sp_trace_setevent @TraceID, 16, 7, @on

This means Event 16 and Column 7 is ON [to be collected in trace]

From the above link we can find the event and column name.

16 -   Attention    -   Occurs when attention events, such as client-interrupt requests or broken client connections, happen.
 
7  -   NTDomainName -   Windows domain to which the user belongs.

For Filter -

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

Step 1: To take the trace

Please open the attached file ErrorTrace.sql in the SQL Server Management Studio, and go through the instructions below.

1) Change N'InsertFileNameHere' to be a valid path on the server and include a file name for the trace files (c:\temp\traces), and extension for the filename is not needed. (You can add/ remove the events as per your need.)

2) Run the script and it will start the tracing, and output a number. Note this number as it will be needed to stop the trace later.

3) Reproduce the issue

4) Using the trace # from Step 2, run the following

a. Sp_trace_setstatus trace#, 0

b. Sp_trace_setstatus trace#, 2

At this point, the trace will be stopped.

Step2: Load the trace into a table and analyze

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

Run all kinds of Operations on the trace just like a table with all the SQL query powers at hand.

Happy Tracing ! :)

Hope it helps!

Umair Khan

Support Escalation Engineer | Microsoft System Center Configuration Manager

Disclaimer: This posting is provided "AS IS" with no warranties and confers no rights.

ErrorTrace.sql

Leave a Reply Cancel reply