Should You Gather Performance Metrics Yourself?

Last month, I talked about the automatic tuning tools that suggest which indexes or partitioning schemes might help improve your SQL Server query performance. The designers of these tools must have a deep understanding of how the SQL Server optimizer works and how queries are processed. The tool designers frequently assume that the users of these tools won’t have such knowledge, so the tool fills that gap.

But there are other tools available that perform tasks that don’t require an intimate knowledge of SQL Server’s secrets and can be created by any developer who can turn data into a nice graphical presentation. When I see tools like these, as I did last week at Microsoft’s TechEd, my first thought is usually "What do I need this tool for? The information presented is easily available to anyone with access to SQL Server."  If I really needed this information, I could get it myself. 

I have the same thoughts at other exhibitions, not just at technology conferences. For example, when I go to a craft show and see a nice quilt or sweater, I might stop and appreciate the workmanship and artistry, but if it’s something I could make on my own, I would never consider buying it. (If my husband is with me, I have to make it clear to him that I’m only examining the item, not thinking about buying it. Otherwise, if I look at something long enough, he thinks it means I would like it as a gift.) And I hesitate to buy SQL Server tools if they’re just alternate ways of presenting information is that already available through metadata, tracing, and performance counters.

I’m sure most of you can see where this is going. I could make the quilt myself, but will I?  Perhaps someday, once I retire, but not any time in the near future. With SQL Server, I could gather all this information myself, but do I? I have a usual set of queries that I run against the metadata (dynamic management views—DMVs—and system views), I have some common traces that I run, and I have metrics that I regularly gather from the performance monitor counters. But then I have to correlate all this information, and it’s up to me to notice any anomalies or red-flag situations.

You might notice that I’m not mentioning any specific SQL Server tool sets here. I don’t want it to appear that I’m recommending (or complaining about) any particular vendor’s tools.  However, if you do an online search for “SQL Server Tools” or “SQL Server Management Tools,” most of the main vendors will pop up on the first page.  I’m not implying that the tool vendors don’t have deeper SQL Server expertise than many DBAs out there; I know for a fact that some of them have real SQL Server experts on their design teams.

Although the tools might present information that you could get for yourself, you need to ask yourself if you would actually gather all this information on your own and be able to quickly recognize where the problem areas are. Would you be able to use your own methods to convince clients, co-workers, or management that you have detected problems? Would you be able to gather data from dozens (or perhaps hundreds) of SQL Server instances and compare the results from many different databases? Maybe the answer is yes, but most likely it’s no. Many of the terrific graphical SQL Server management and tuning tools present the information in a customizable format that lets you choose what you want to see and quickly recognize problems. 

I realize as I get older that I don’t have to do everything myself. Convenience is looking more and more attractive to me, and efficiency has always been high on my priority list. If convenience and efficiency are also important to you, and if you have better things to do with your time than re-invent the wheel, you might consider letting everyone do what they do best. Let the tool developers give you an analysis of your problem areas and you can use your time to tune and manage your SQL Server systems.