sql: extract statement & duration from large trace file

sfun28
sfun28 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008

Commented:

Author

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This puts the 2 columns you want into a table.

SELECT TextData, Duration
Into [C:\Path\To\File.trc]
FROM fn_trace_gettable('C:\Path\To\File.trc', default)
ORDER BY duration desc

You can use SSIS or other tool to export the table out again.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Awarded 2008
Awarded 2008
Commented:
SELECT top 100 textdata, duration
FROM fn_trace_gettable(‘C:\yourtacefilename.trc’, default) Trace
order by duration desc

Author

Commented:
SSIS is a beast...is there another tool you'd recommend to export?  is .trc just a csv file or a proprietary format?
Awarded 2008
Awarded 2008

Commented:
used to proprietary...think it is just xml now

why do you need to export?  you can use BCP to export.

Author

Commented:
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.
Awarded 2008
Awarded 2008

Commented:
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.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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.
Awarded 2008
Awarded 2008

Commented:
No, not a waste of time at all...especially if you have limited space and don't want the overhead of saving everything.

Author

Commented:
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 )
Awarded 2008
Awarded 2008

Commented:
awesome!

Author

Commented:
thanks!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial