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
