I love tools that make my job easier; I particularly love when those tools are free.

One fantastic example is ClearTrace. Though it is simple in design, its purpose and use are clear: analyzing trace output for SQL Server 2005 performance issues. I have used this application numerous times and in many different scenarios.

There have been two scenarios in particular where ClearTrace came to the stage in spades. The first I actually posted about a few months back: a colleague and I were troubleshooting SharePoint 2007 performance issues. I took a trace of the database, ran ClearTrace against it, and we came up with a solid starting point for our investigation into the inner workings of MOSS…… (enough of that here, I’m not a MOSS expert, so I won’t keep going!).

My second, and more recent example, was on a project involving a custom web application. The database hardware and setup our client had was not optimal for the solution we were rolling out, but it is what we had to work with, and what they were willing to install, so we made the best of it.

We were looking to squeeze out as much performance from these machines as possible; the client provided us with some traces from production database where the first wave of new users had hit the machine and loaded them into the ClearTrace application.

I will make a note here that the application architecture and database design is damn solid – we had a good team working on this and the end result was a very well performing application that more than met the business needs.

Still, some of the results were surprising to the development team – in this case mostly due to users and their old habits, but the big bonus that came out of this was two-fold: (1) we tuned procedures and indexes and optimized the disk subsystem based on the results and (2) found out usage patterns in the data layer that had not been predicted.

ClearTrace is a performance related tool. One important note is that this tool does not work with SQL Express as this edition does not install the SMO library (just pick up SQL Developer Edition, cheap and it has all the features!).