Freigeben über

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

  • Artikel

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

  • Anonymous
    August 11, 2011
    What is the trace was created using the Profiler gui ?  

  • Anonymous
    August 11, 2011
    It works with that too - just save the trace as a Trace File (.trc).

  • Anonymous
    December 10, 2014
    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.

  • Anonymous
    March 15, 2017
    Great Help. Working with Azure Database trace as well.