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

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

  • Comments 0

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 (Lohndorf)

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post