Nav developer: How to read a SQL Profiler trace file into a SQL table

[Blog bot]

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:xMyTrace.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 ORDERBY 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.






Читать дальше