Sql Server : How to read Sql Profiler trace files into a SQL table
- Posted by Sqltimes
- On April 6, 2014
- 0 Comments
Quick one today:
Once in a while we all need to capture SQL traces from production or development database server and need to analyze it. Sql Trace files is a binary files that could be opened in Sql Profiler. Often times, it is easier to analyze the trace thoroughly when you load the data into a table, allowing you to run diagnostic queries to find out evidence to the question you are looking for.
Below is a method used to load Sql Trace file into a SQL table.
|
1
2
3
4
5
6
|
---- Load trace into a table--SELECT * INTO TraceTableFROM ::fn_trace_gettable('T:\Trace\LockEscalation.trc', default)GO |
As you can see, the first parameter is the location of the trace file; Second one is to indicate the number of such files to be loaded. Often times, trace files, based on how trace is captured, end up being spread over multiple files. So the second parameter allows us to read specified number of files to be loaded into the SQL table.
If you specify ‘default‘, SQL loads all matching files in that location.
Make sure you create indexes on necessary tables to improve your analysis query performance.
Hope this helps,
0 Comments