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

http://blogs.msdn.com/b/nav_developer/archive/2009/07/07/read-a-trace-file-into-a-sql-table.aspx

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