Matthew McGiffen has a two-parter for us. Part 1 covers useful tools for performance troubleshooters:
When you’ve got the symptoms of a database issue you can run a series of diagnostic queries to try and drill down on the problem and then start figuring out solutions. There are a number of free tools out there though that can speed up that process immensely. In this post we’re going to look at my favourites.
Part 2 takes us through the mechanics of measuring query performance:
Attendees have come up with a range of answers from “With a stopwatch” (which I like a lot) to the slightly more technical “Using Profiler”. I like the first answer just because that’s where we all start. We run something from an application and we literally time how long it takes, or we run something in SSMS and we use the counter near the bottom right of our query window to tell us how long it took. We may even run something 100 times in a loop and capture the overall time so we can take an average.
Incidentally, if you are using SET STATISTICS IO ON and are sick of the way it writes out results, Richie Rump’s Statistics Parser tool is great for converting the blob of text into something humans can easily parse.