sfun28
asked on
sql: extract statement & duration from large trace file
Folks, I used Sql Server Profiler and let it run for a while. I now have a fairly large (8GB) trace file. I'm only interested in getting the SQL statements besides the duration for Sql:BatchCompleted events. This can either be in the form of a table or exported to Excel. Basically I want to sort on Duration and see which queries are taking the longest. So ideal format is:
Sql Statment, Duration
Is there a way to do this without importing the whole trace file? Could you provide specific instructions on how to accomplish this?
Sql Statment, Duration
Is there a way to do this without importing the whole trace file? Could you provide specific instructions on how to accomplish this?
ASKER
yes, I'm aware of this command. Can you provide specific syntax to get the output I'm looking for? Its taking too long to import the whole table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SSIS is a beast...is there another tool you'd recommend to export? is .trc just a csv file or a proprietary format?
used to proprietary...think it is just xml now
why do you need to export? you can use BCP to export.
why do you need to export? you can use BCP to export.
ASKER
This query is taking a while. Is there a WHERE clause I can use to limit to Sql:BatchCompleted events?
I just need the data in some kind of table format. That could be a table in SQL or a table in Excel. either way I'll be able to better manage the data.
I just need the data in some kind of table format. That could be a table in SQL or a table in Excel. either way I'll be able to better manage the data.
if it is that big, it is going to take awhile because it has to scan it all. You're probably better off biting the bullet and taking the time to get it into a table. From there, you can query it all you want (and index it so it is faster). You can use the INTO clause of the SELECT statement to get it into a table.
See my earlier comment which puts it into a table.
Select top against a trace file is kind of a waste of time, since it scans everything. Might as well save while scanning, then when you are 100% you no longer need data, drop the table.
Select top against a trace file is kind of a waste of time, since it scans everything. Might as well save while scanning, then when you are 100% you no longer need data, drop the table.
No, not a waste of time at all...especially if you have limited space and don't want the overhead of saving everything.
ASKER
This seems to works great:
SELECT TextData, Duration
Into [PerfTemp]
FROM fn_trace_gettable('PATH HERE', default)
WHERE ( Duration is not null ) and ( duration > 0 ) and ( textdata is not null )
SELECT TextData, Duration
Into [PerfTemp]
FROM fn_trace_gettable('PATH HERE', default)
WHERE ( Duration is not null ) and ( duration > 0 ) and ( textdata is not null )
awesome!
ASKER
thanks!!!
http://kalpeshshirodker.wordpress.com/2008/02/15/reading-sql-trace-files-using-fn_trace_gettable-function/