How to read a SQL Profiler trace file into a SQL table


Just a small tip that I use often when I have a large SQL Profiler trace. Rather than reading through trace files line by line, you can read it into a table and then query it. This syntax creates a new table in SQL Server and loads your trace file into it:


SELECT * INTO MyTraceTemp


FROM ::fn_trace_gettable(‘c:\x\MyTrace.trc’, default)


 


The default parameter means that if the trace is across multiple trace files, it will automatically read the next trace file(s) too. If your trace consists of a lot of trace files you can limit how much you read by setting it to for example 3 to just load the first three files.


Once you have the trace file in your table it might be a good idea to add indexes on the table, for example an index for Reads and one for Duration. Then query it, like this for example:


SELECT Reads, Duration, * FROM MyTraceTemp ORDER BY Reads DESC


 


 


Lars Lohndorf-Larsen


Microsoft Dynamics UK


Microsoft Customer Service and Support (CSS) EMEA


These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.


 

Comments (4)

  1. Rick Willemain says:

    What is the trace was created using the Profiler gui ?  

  2. It works with that too – just save the trace as a Trace File (.trc).

  3. John Heimiller says:

    Nice tip….I often save profiler traces to tables from the GUI but had not a need to take a trc file to table until recently…thanks.  Working with XE now…hope its as flexible.

  4. Anupam says:

    Great Help. Working with Azure Database trace as well.

Skip to main content