Cet article est disponible en francais.

After listening to the RunAs Radio show #103 with Bill Graziano, I decided to give a try to his tool, ClearTrace, an SQL trace analysis tool.

It turns out that I’m in an SQL optimization spree recently, and the project I’m working on had an complete sequence of operations that took more than 24 hours to complete. Analyzing traces with the SQL profiler can be time consuming­ - ­­­ needle in a haystack consuming - particularly when the log is over 7GB in size, in that case.

Finding small queries that are executed thousands of times is rather hard to track, and finding proper candidates for optimization is a bit complex. You don't want to spend time optimizing a query that has a small impact.

This is where Bill’s tool come into play. Give it a trace file, the tool analyses it and gives you aggregate information about what takes the most CPU/Duration/Read/Write. Pick your victim.

After a few hours and a few runs of ClearTrace to find which stored procedure needed rework, I found a bunch of store procedure that were executed thousands of time and that were using a lot of cumulative I/O. After optimizing these procedures, the whole process that took more than 24 hours is now down to about 7 hours.

Nothing magic here, the key is to find what to optimize on long running processes executing millions of queries. Bill’s tool does that perfectly !

On a side note, at first ClearTrace threw an out of memory exception after trying to import my big trace file. Turns out that after exporting it with Reflector and debugging the code, I spotted a small refactoring issue that Bill fixed very quickly. Thanks Bill !

As Carl Franklin says in .NET Rocks' “Better Know a Framework”, learn it, use it, love it !