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
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
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 !