Reading a SQL Profiler Trace file

Reading a SQL Profiler Trace file

In SQL Profiler you can open data from a file or a table with File -> Open menu option. If you have your data in multiple rolled over files SQL profiler will ask you if you'd like to open the next file after it reaches the end of the current file. You can also replay your trace using the Replay menu in which you can even set breakpoints.

Personally I like to analyze my trace files with SQL. To do that you have to have the data in a trace table. If you have a file you should import that file into the table.

The only way to do that is by using the fn_trace_gettable built-in table valued function:

select *

from fn_trace_gettable('c:\MyTraceFile.trc', default)

The first parameter is obviously the path to the trace file on the server's disk. The second parameter is the number of rolled over files to be read. The default value is -1 which means all files are read. If you want to read just one of many rolled over files use 1 as your parameter value.

Although you could use the function to read the trace file each time, the better way is to simply import the trace into a table and query the table itself since reading the file each time is slow. A simple way to do this is:

select IDENTITY(BIGINT, 1, 1) AS RowNumber, *

into MyTraceTable

from fn_trace_gettable('c:\MyTraceFile.trc', default)

where 1 = 0 -- just create the table without any data

After you have the table definition you can index it to your liking and then simply use insert to populate the table:

insert into MyTraceTable()

select

from fn_trace_gettable('c:\MyTraceFile.trc', default)

Combining SQL Profiler Trace file and PerfMon log file

Start SQL Profiler and open a trace file in it. The SQL Trace file and the PerfMon log file must be time synchronized. So either you have to be gathered on the same machine or the 2 machines have to be time synchronized. Wait until the SQL Trace file is fully loaded. Then go to File -> Import Performance Data...

Choose the PerfMon log file (.BLG, .CSV) that correlates with the SQL trace file. A dialog will open asking you to select the counters you'd like to display. Click OK and you should get a screen looking similar to this:

Picture 4: Combine SQL Profiler Trace file and PerfMon counters log file

By clicking on the PerfMon graph you can see that the selected row in your trace changes. The PerfMon counter has the minimum polling interval of 1 second so you will get more than one sql event per PerfMon counter and the selected row in the trace is the row that best corresponds with the PerfMon counter at that time.