22 June 2007 10:02 tonyrogerson

Reading large Profiler Trace files

What was I thinking? I double clicked on a profiler trace file, only 5GB and up it started to come in Profiler; I got distracted (the kettle boiled) and came back and realised my machine was strugglging a bit - strange, its a good workstation - 4GB, fast AMD etc...

Oh dear, then it dawned on me what I'd done, I was opening a 5GByte file in Profiler - it's trying to load it all into memory - lol - whoops....

What I should have done is this...

SELECT *

INTO prof

FROM fn_trace_gettable('d:\temp\TraceActivity.trc', default);

GO

I guess the real advice here is this a) shouldn't be done on your live server unless you are using smaller trace files (10's-100'sMBytes depending on how much memory you have and how busy your system is) and b) profile into a table in the first place, in a database (and preferably a different server) so all you need to do is back it up, zip and send; or analyse away. Alas, if you are using server based traces rather than through profiler you have little choice than the file approach.

There was a really useful tool for loading the files and parsing the contents so you can group queries but I can't find it - if anybody knows then please feel free to comment.

Tony.

Filed under:

Comments

# re: Reading large Profiler Trace files

22 June 2007 12:42 by ACALVETT

Well, there is read80trace which is a nice little tool for SQL 2000. You can get it at. You can do stress testing as well etc etc. http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

To the best of my knowledge, MS has not updated the tool for SQL 2005. But one of the guys over at SQLTeam.com wrote cleartrace which i hear fills the gap well (not had a chance to try it myself yet).

http://www.cleardata.biz/cleartrace/default.aspx

# re: Reading large Profiler Trace files

22 June 2007 14:27 by simonsabin

Clear trace is very neat little tool.

# re: Reading large Profiler Trace files

22 June 2007 14:44 by bfrasca

ClearTrace reads SQL2000 (80) and SQL 2005 (90) trace files.  There is a READ90TRACE but as far as I know, Microsoft hasn't released it for public consumption yet.

# re: Reading large Profiler Trace files

22 June 2007 17:01 by Askedal

I have to handle many of these tracefiles in my daily business. I read them into a table, create some indexes and a view over it to get better output. And i have a stored procedure to get the access plans in the Query analyzer as well. With T_SQL i can group and search whatever i want, more than every tool can provide.

# re: Reading large Profiler Trace files

22 June 2007 17:34 by benjones

There is also SQL Nexus, developed by Ken Henderson, which will do what you want.  It will, amongst other things, perform Trace Analysis and present the data in a nice format.  You can download it from here: www.sqlnexus.net.  The tool is really like an amalgamation of the performance dashboard reports and ReadTrace.