I have to admit that when I had a performance problem with some code, I would often fire up Profiler and try to trace events that were occurring right then. If you’ve used Profiler, it was clunky in v6.5, slightly worse in 7,2000, and it’s very complicated and lots of options. And if you forgot to enable some of them, you lost the data.

Now you can set up custom trace templates that will include the custom fields and events you need, but those aren’t always on each system, especially if you end up connecting to a server and trying to trace things there. And if you want to customize it for a specific set of circumstances, you either end up with tons of trace templates, or you waste time setting up new traces.

I saw in a recent post that Erland Sommerskag had posted a new sqltrace stored procedure on his site. It was written by a friend and it takes a batch as a parameter and then traces the current connection while executing a batch.

It’s simple, and it fills a quick need that can help you to easily and quickly get performance data about your batches and figure out what is going on. I tested this on my system and got and error because I neglected to closely RTFM that Erland wrote. I don’t have a c:\temp, which is the default location for trace files. You can easily change this in the source code, or even include it as a parameter (which I would recommend).

In any case, I’d urge you to check it out. It’s a quick and relatively-clean solution that can help you get quick performance information on batches of code.