SQL Server Profiler: Overcoming the Difficulties

Greetings,
Two weeks ago, I encouraged you to spend more time with SQL Server Profiler. I told you that Profiler is the most powerful weapon in your tuning arsenal because it can help you precisely define and measure performance problems. Many people wrote to say they agreed, but most of them also qualified their statements, as did the following readers:

First, I couldn't agree more that Profiler is woefully lacking in useful documentation that explains how to productively use the tool. But I chatted with the SQL Server Magazine editors, and you can look forward to detailed Profiler coverage in upcoming issues. In addition, I'm going to write a series of Web-exclusive features that will discuss the most important lessons I've learned through years of analyzing Profiler output. Feel free to share your tips with me. I'll make sure the best ones appear in SQL Server Magazine so that everyone can benefit from them.

Second, Profiler output often does become so verbose that you can't see the forest for the trees. When I analyze Profiler output for my clients, I rarely look at more than six events and a handful of data columns. In fact, if you feel overwhelmed by Profiler, you might want to start with the following two events:

'Stored Procedures --> RPC:Completed' 'TSQL --> SQL:BatchCompleted'

and the following data columns:

EventClassTextDataApplicationNameLoginNameCPUReadsWritesDurationSPIDStartTime

I also agree with the readers who said that Profiler's GUI is close to useless for analyzing trace outputs, which contain too much information to absorb even if you limit your events and data columns. The real trick to taming Profiler is to load the data back into SQL Server for analysis rather than using the Profiler GUI to sort through the trace.

Less is absolutely more. Think of Profiler trace analysis as an exercise in data warehousing. Data-warehouse users hardly ever care about a single event in their databases. They care about aggregations and trends. Profiler analysis needs to work the same way. You'll rarely find the answer if you look at individual SQL statements in a vacuum. You must find ways to aggregate the transactions. I don't have enough space in this column to explain how I prefer to aggregate Profiler data. So until my Profiler Web-exclusive series hits the presses, you might want to read the following articles:

I wrote the tuning article more than 2 years ago based on my work with SQL Server 6.5's SQL Trace tool, and many of my current analysis techniques grew out of that work. SQL Profiler is very different from SQL Trace, but my analysis techniques for both are similar.

Profiler analysis is one of my favorite subjects, and I'd be happy to help you master this confusing but valuable tool. I can't promise to answer every question, but I'll do my best. And don't forget to send me your clever analysis approaches.