Author: mikevanzap

I would like to target in next posts on comparing Extended Events with Profiler Traces. In this post we will look at basic T-SQL routines creating and handling SQL Traces. You can use SQL Profile tool too. Open Management Studio -> Top menu -> Tools -> SQL Server Profiler.

Just few briefly words about SQL Traces. It is used to track SQL Server events triggered in system. Such a tool can be used to monitor SQL Server deadlock, performance tuning, auditing security area, etc.

Deeper comparation with extended events will be mentioned in one of my next posts.

As extended events SQL Server trace have few options:

Data column is an attribute of an event that can be collected in trace. Not all attributes are available for all events. Each event has its own set of attributes. Usefull queries for traces and their binding to columns bellow.

SELECT * FROM sys.trace_columns
SELECT * FROM sys.trace_event_bindings

Event is an object that is triggered in system and tracked by trace. The event contains data columns that can be collected and reported in trace.

SELECT * FROM sys.trace_events
SELECT * FROM sys.trace_categories

Trace is actually a collection of events and data returned by the Database Engine. To get info about traces and their options use following query.

SELECT * FROM sys.traces

Trace filters are predicates limiting collected events in a trace. To get info of filters set to concrete trace use following function with ID of trace as parameter.

SELECT * FROM sys.fn_trace_getfilterinfo(2)

To collect trace data by T-SQL you have to do few steps.

1. create trace – to get its ID and define attributes like destination etc.
2. set events to trace – events that will be collected by trace
3. set filters if requested – to filter event data
4. run/stop/remove trace

See detail info bellow.

To create trace use following code.

declare @TraceID int
declare @maxfilesize bigint
declare @rollOver    int = 2
declare @path NVARCHAR(100) = N'C:\Trace\Test'
set @maxfilesize = 1000000 
declare @maxfilecount INT=20
Exec @rc = sp_trace_create @TraceID output, 	@rollOver /*enable rollower*/, @path, @maxfilesize, NULL ,@maxfilecount
if (@rc != 0) goto error

The most important parametres are path, targeting destination of trace data. There are also possibilities to get data outputu to database table or use SQL server profiler application. In example above we use filesystem destination. @TraceID parameter gets ID of trace assigned by system. You use this ID when referencing trace in other routines. You can get ID from system table sys.traces too.

Other parameters defined in our examples are @maxfilesize – you define size of destination file in magabytes. If trace data achieve defined maxfilesize, the trace will be stopped.

In case that you would like to have trace data distributed to more files, because of faster quering, you can set rollover functionality as you can see on our example.  Value set for @rollOver  parameter tells the trace to establish new file one the previous one is full. Through this parameter you can set more options, you can see this link to get detail info.

By creating trace with this procedure we still not getting data. We have to set the trace which data should be collected. So use another one stored procedure to set this option.

declare @on bit
declare @eventID INT = 14
declare @TraceID INT = 2
set @on = 1
exec sp_trace_setevent @TraceID, @eventID, 10, @on
exec sp_trace_setevent @TraceID, @eventID, 3, @on
exec sp_trace_setevent @TraceID, @eventID, 11, @on
exec sp_trace_setevent @TraceID, @eventID, 7, @on
exec sp_trace_setevent @TraceID, @eventID, 8, @on
exec sp_trace_setevent @TraceID, @eventID, 12, @on
exec sp_trace_setevent @TraceID, @eventID, 14, @on
exec sp_trace_setevent @TraceID, @eventID, 35, @on

The first parameter @TraceID is used to set trace ID. @eventID is ID of event you can find in system table  sys.trace_events. In our case eventid 14 is set. It means Audit Login data are collected.

As next parameter you set columns, attributes you would like to collect. You can get list of columns with their ids from system table sys.trace_columns or from MSDN web here. For example on first row where sp_trace_setevent is executed we set 10 which means that application name attribute is collected.

The last parameter for sp_trace_setevent procedure is bit marker. All columns are disabled by default, by specifiying bit to 1 we enable the attribute to be collected.

To filter traces use stored procedure sp_trace_setfilter.

sp_trace_setfilter [ @traceid = ] trace_id   
          , [ @columnid = ] column_id  
          , [ @logical_operator = ] logical_operator  
          , [ @comparison_operator = ] comparison_operator  
          , [ @value = ]

Define @traceid to specify to which trace you would like to apply filter. By specifying @columnid you define on which attribute will be filter applied. @logical_operator means that OR (1), AND (0) filter logic will be applied. @comparison_operator (LIKE, equal, not equal, etc.) defines comparison_operator by its IDs you can find here . IN @value we define value to which should be column compared.

exec sp_trace_setfilter 2, 11, 0, 6,N'sa'

In filter above we set that on our trace monitoring Audit Login event, SA user will be filtered in the trace.  Trace=2, LoginName=11, 0 =AND operator, 6 = LIKE operator, N’sa’ value to be compared.

To handle trace activity, state, there is sp_trace_setstatus stored procedure implemented. See examples bellow.

exec sp_trace_setstatus 2, 0 /*stop trace*/
exec sp_trace_setstatus 2, 1 /*run trace*/
exec sp_trace_setstatus 2, 2 /*remove trace - has to be stoped first*/

Next time look at traces in more detail and compare them with Extended Events. Stay tuned!