Using T-SQL to read through Profiler Trace files

SQL Server Profiler Traces are used to capture activity running against the Sage 500 databases.  The Profiler GUI has different ways to find errors, exceptions, names of objects. There are also Filter criteria that can be used to narrow down what is captured. 

There is a T-SQL function that can be used to query the profiler trace.  The function is called fn_trace_gettable.  It has two arguments 'filename' and 'number of files'.  Filename is the path and name of the trace file that will be read. The number files indicates the number of rollover files to read. Its easier to use DEFAULT as the number of files, because the function automatically reads all or just the one file. 

Here is a basic example

SELECT  *  FROM fn_trace_gettable('C:\database\tracefiles\Tracecaptured.trc', DEFAULT)

It is just like running queries against a table. For example, if you wanted to show a list of all columns where a row contains ApplicationName with Sage 500 within it, you could try something like the following:

SELECT  *  FROM fn_trace_gettable('C:\database\tracefiles\Tracecaptured.trc', DEFAULT) WHERE ApplicationName like 'Sage 500%'

You could also run a query to show long running T-SQL statements.  It does appear that the Duration column is being returned in microseconds. So, if you compare the value seen from the actual profiler trace to the result from the query below, the numbers look different. The Profiler trace tool has an option (Tools, Options, Show values in Duration column in microseconds) where you can show the result in milliseconds or microseconds.  To make the T-SQL query easier to read, translate that into seconds as shown below.  The following example returns rows that have duration times larger than two seconds:

SELECT  Duration/1000000, TextData, ApplicationName WHERE ApplicationName like 'Sage 500%' and  Duration/1000000 > 2

If you wanted to save the data into a -TSQL table, you can also do that as well. For example, if you wanted to store all the rows from the profiler that originated from a specific user's machine into trace into a table, try the following:

SELECT  *  INTO TraceTable FROM fn_trace_gettable('C:\database\tracefiles\Tracecaptured.trc', DEFAULT) WHERE HostName ='UserMachine10'

I have found value using the  fn_trace_gettable function when the profiler trace is very long. Yes, you can filter the trace and exclude rows, but sometimes it can take a long time waiting for the output to be refreshed.  The fn_trace_gettable function seems to accomplish it faster.  If you have used this function before, please let us know what your experience was.

Anonymous