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 |
|
To query the table |
SELECT * |
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.