I have traced some data into a trace table into Sql Server using the Profiler GUI. This table has the following structure:
It contains the SQL statements as well as execution plans in the TEXTDATA column. I figured out that usually the execution plan xml that belongs to a select statement has been placed into the row before the sql statement.
So for example the execution plan for the Select statement in row with [ROWNUMBER] = 60 usually can be found in the row with [ROWNUMBER] = 59.
This seems to be quite resilient (enough for my purpose) and I would like to keep this relation for later use. (So far I could not find another way of catching both, the query and the plan - ideally it should come as ONE merged result, for example the plan attached to the query inside of a comment... but I could not manage to create that.)
However, to avoid the massive overhead of using the Profiler GUI I created a trace script, stored the results into a trace file (because unfortunately using a script it's not possible to directly redirect the results into a table) and then imported the trace file into a table using the following statement:
SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\traces\myTraceFile.trc', default)
Unfortunately this produces a different table structure and worst about that is, that the ROWNUMBER identity column is now missing, so the records are messed up and it's impossible to find the plans that belong to the queries.
Q 1: Is it possible to create a table from the trace file that has the rows stored in an order similar to the trace table profiler had created?
Q 2: Does anyone know another better way to get those information without using the profiler GUI?