Greetings,
"I'm constantly amazed and disappointed by the low number of SQL Server developers and administrators who use SQL Server Profiler on a regular basis." That's the first sentence of a SQL Server Magazine UPDATE commentary I wrote 15 months ago. And it's time drag out the same soapbox.
I reviewed the past two quarterly reader rankings of the most popular topics in SQL Server Magazine. Application performance/tuning carried the popular vote for the past two quarters, while SQL Profiler flopped around in the middle of the pack with rankings of 12 and 17 for the past two months, respectively. That vote can only be explained in one of two ways: Serious voter irregularities, in which case I demand a recount, <g> or more likely, most people simply don't realize that SQL Profiler is far and away the most important SQL tuning weapon in their arsenal.
Never heard of SQL Profiler? For the uninitiated, SQL Profiler ships with SQL Sever and lets you see statements that the SQL Server client passes to the server. SQL Server 2000's SQL Profiler lets you track 41 data points (such as Duration, server process ID, or Reads) across 99 events (such as SQL statement started or Lock Acquired). The amount of engine-level detail SQL Profiler provides is simply amazing.
Why is SQL Profiler such an important aid in the eternal fight against poor application performance? Let's start with a more generic question. What's the first step in solving any problem? You must first understand and define the problem. SQL Profiler is an important tuning tool because it lets you precisely measure what your application actually does with your precious and limited database resources. Without that understanding, you're simply guessing when you try to define an application-level performance problem.
Trust me on this one. Most serious tuning problems begin somewhere in the application. Even indexing strategy is essentially an application-level issue. After all, indexes are used to speed queries and queries are generated by applications. So, the application drives the appropriate indexing strategy. If it's true that applications initiate performance problems (and it is), you can't properly tune your system without knowing how the application interacts with SQL Server. Here's the closing line from that 15-month-old SQL Profiler commentary, and it still applies: "Stop the madness and start using SQL Profiler today!"
