Waits and Queues: Performance-Tuning Gems

Performance tuning is my favorite topic and what I spend most of my professional time doing. I have a good sense of the performance-tuning techniques available, so it's rare that I run across practical performance-tuning information that I've never seen or thought about before. However, in SQL Server Magazine's January issue, Microsoft's Tom Davidson provided performance-tuning information that was not only some of the most important performance-tuning material to come out in several years, but also brand-new to me (see "Opening Microsoft's Performance-Tuning Toolbox"). I don't usually review SQL Server Magazine articles, but I'm going to make an exception this week.

Tom is a member of the Microsoft SQL Server Development Customer Advisory Team and a program manager for the SQL Server development organization. The Customer Advisory Team works on the most interesting SQL Server problems and challenges that customers face around the world. Tom and his colleagues have developed a methodology that uses waits and queues to systematically track down and resolve customers' SQL Server performance problems. The methodology uses several SQL Server sources to analyze the aggregate effect that various waittypes and other queues have on an application's performance. (For details about what waits and queues are, please see the article.) Why is this performance-tuning method so important? Two reasons: The methodology will inevitably lead to performance improvements in various parts of your applications, and it's the first truly new SQL Server 2000 performance-tuning information that I've run across in several years.

In my opinion, any practical information about performance tuning is exciting. Although the basic ideas in Tom's article aren't new, he's pulled together a comprehensive resource that describes all the waittypes you might see in sysprocesses. He describes what the waittypes mean and, even more importantly, describes how to interpret the waittypes in a real-world performance-tuning scenario. Very cool.

Since the article came out in January, the Customer Advisory Team's methodology has been invaluable to me when analyzing dozens of performance problems. If you've already read and acted on Tom's January article, good for you. If you haven't, take the time to read and consider the information it contains. If analyzing waits and queues information can help me solve performance problems, it can help you, too.