Managing and analysing multiple trace files
Hi.
Have you ever set off a profiler trace and then realised that you’ve amassed tons and tons of data and now you need to start searching for that needle in a haystack?
If the answer to this question is yes then you might be interested in a pretty cool SQL function that lets you read the trace files and, even better, import them into a table.
Consider the following SQL statement:
FROM ::fn_trace_gettable(‘D:\DBAScripts\output\sql_trace_file.trc’, default);
This will import your SQL trace files into a table, after which you can add indexes to optimise the numerous queries you will no doubt have to run against the data.
Let me just break down the syntax of that statement to try and give you a better understanding of how you can best utilise this feature.
The SELECT * INTO is straight-forward stuff so I won’t bother with that bit, let’s just concentrate on the function syntax.
::fn_trace_gettable is the actual name of the function we are using. The two parameters within the parenthesis basically specify the full path and file name and the number of trace files you want to import. By specifying default you are effectively saying that you want all of the trace files that have been created under that name. Multiple trace files of the same name will be suffixed with an underscore followed by a number, for example, a trace file called sql_trace.trc may have rolled over a number of times to create sql_trace_1.trc, sql_trace_2.trc etc. If you only want to import the first 5 trace files then just specify the number 5 instead of ‘default’. Simple.
So as you can see, you have a pretty handy little function that can import all of that trace data into a SQL table which you can then slice and dice until your heart is content.
Enjoy
PD
