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:
- "I think the problem is figuring out how to use SQL Profiler. I'm dying for a book that deals only with using Profiler and that has about 200 pages of in-depth material, rather than the standard how-to's I've run across. Any ideas?"
- "I agree that Profiler is a valuable tool, but I've found it difficult to use. The results it produces are always verbose, too verbose to filter. And although power users might disagree, Profiler needs to be simplified. In this case, less really is more."
- "Can you ask the SQL Server Magazine editors to start a series that discusses all the Profiler parameters? Break them into categories based on interest to DBAs or developers. DBAs, for instance, would be more interested in locking and logins. Developers would be more interested in cursors and transactions."
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:
EventClass TextData ApplicationName LoginName CPU Reads Writes Duration SPID StartTime
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:
- Itzik Ben-Gan's "Problem-Solving with SQL Profiler" and "Trace That Event with SQL Server Profiler"
- My "Tuning Database with SQL Trace"
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.
