RMJCS Logo
 

Using the Default Trace

 
 

This article was originally inspired by Tim Chapman's article "Monitor system information with SQL Server 2005's default trace" on TechRepublic.

The default trace is a server side trace by default started at server startup. It is a lightweight trace that records a number of server and database events and has little impact on the server - Linchi Shea has done a nice analysis of the actual impact of this trace; "Performance Impact of Enabling Page Checksum and Default Trace". Even if you have no particular problems with your server it's worth leaving this trace enabled and keeping an eye on the contents to make sure there is nothing unexpected.

To see if the default trace is running (which it should be unless someone has explicitly disabled it) use the following;

sp_configure 'show advanced option', '1' -- reset afterwards if necessary.
reconfigure
go
sp_configure 'default trace enabled'

To disable/enable the default trace use the following;

sp_configure 'show advanced option', '1' -- reset afterwards if necessary.
reconfigure
go
sp_configure 'default trace enabled', 0 -- or 1 to disable.

To get the trace id for the default trace (required by many of the trace related system functions) use the following;

select id from sys.traces where is_default = 1

To get the list of events in the default trace definition use the following;

select distinct
   t.eventid as EventID,
   c.name as EventCategory,
   e.name as EventName
from
   sys.traces st
      cross apply master.dbo.fn_trace_geteventinfo(st.id) t
      left join sys.trace_events e on e.trace_event_id = t.eventid
         left join sys.trace_categories c on c.category_id = e.category_id
where
   is_default = 1
order by
   c.name, e.name

My SQL Server 2005 Developer Edition, SP2, lists 34 events.

To see the filters applied to the default trace use the following;

select
   tc.name as ColumnName,
   case fi.logical_operator
      when 0 then 'And'
      when 1 then 'Or'
      else 'Unknown'
   end as LogicalOperator,
   case fi.comparison_operator
      when 0 then 'Equal'
      when 1 then 'Not equal'
      when 2 then 'Greater than'
      when 3 then 'Less than'
      when 4 then 'Greater than or equal'
      when 5 then 'Less than or equal'
      when 6 then 'Like'
      when 7 then 'Not like'
      else 'Unknown'
   end as ComparisonOperator,
   fi.value as [Value]
from
   sys.traces t
      cross apply master.dbo.fn_trace_getfilterinfo (t.id) fi
         left join sys.trace_columns tc on tc.trace_column_id = fi.columnid
where
   is_default = 1

My SQL Server 2005 Developer Edition, SP2, shows no filters for the default trace.

To load the default trace from the file into a temporary table use the following;

select
   gt.*
into #Trace
from
   sys.traces t
      cross apply master.dbo.fn_trace_gettable(t.path,default) gt
where
   is_default = 1

BOL states that the fn_trace_gettable function is safe to use on active trace files although on a busy server, if a lot of data is read from multiple files then there will be increased disk contention between the reading and writing to the file.

My SQL Server 2005 Developer Edition, SP2, has 65 columns in the default trace. The following query displays a few of these columns to give you a summary view of the event data produced by the preceding query;

select
   t.EventSequence,
   tc.name as EventCategory,
   te.name as EventClass,
   tsv.subclass_name as EventSubClass,
   t.SPID,
   t.Success,
   t.Error,
   t.TextData,
   t.StartTime,
   t.Duration,
   t.DatabaseName,
   t.ObjectName,
   --t.HostName,
   --t.ApplicationName,
   t.LoginName,
   t.DBUserName
from 
   #Trace t
      left join sys.trace_events te on te.trace_event_id = t.EventClass
      left join sys.trace_subclass_values tsv on tsv.trace_event_id = t.EventClass
                                             and tsv.subclass_value = t.EventSubClass 
         left join sys.trace_categories tc on tc.category_id = te.category_id
order by 
   eventsequence

An excellent reference for tracing and profiling on SQL Server 2005 is chapter two in Kalen Delaney's book 'Inside Microsoft SQL Server 2005: Query Tuning and Optimisation".

Useful BOL links;

 
     
Copyright 2007-2015 RMJCS Ltd