Tom Davidson, and former Microsoft guy, authored a new paradigm in performance tuning called Performance Tuning Waits Queues. Here’s part of the intro to the paper.
Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective.
I’m not sure he really meant ‘easily’ in the introduction but this performance tuning methodology does give us critical insight in to what’s going on inside SQL Server without, quoting Paul Randal here, “flailing around.” Davidson’s work is the “bible” of Waits and Queues analysis from a SQL Server perspective.
Tom was 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 challenging SQL Server problems from 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 wait types have on an application’s performance.
Davidson’s work has been prosthelytized by another Microsoft guy, Paul Randal. Randal blogs often about the effectiveness of this approach and how to best apply this methodology to real world situations. Randal’s course on Pluralsight is the best introduction to the methodology there is. However, a great starting point is Joe Sack’s presentation on the topic. Sack’s overview is very good and offers and excellent segway into Randal’s course.
If you’re brand new to the world of wait stats then I’d suggest the following course of action to wade deeper into the pool:
- Read Joe Sack’s Presentation
- Read Davidson’s White Paper
- Take Randal’s course
- Read Randal’s Blog titled: Wait Statistics, or please tell me where it hurts
All the links are below in the resource section.
Randal spends quite a bit of what little free time he has blogging about wait stats. A word of caution here, he can go very deep, very fast.
Here’s part of Davidson’s paper that defines what Waits and Queue’s is all about.
The Wait Part
As an application, SQL Server may request system resources as it executes a user query and waits for its request to be completed. Waits are represented by SQL Server wait statistics. SQL Server 2005 tracks wait information any time that a user connection or session_id is waiting. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load. Therefore, SQL Server wait types identify and categorize user (or thread) waits from an application workload or user perspective.
The Queue Part
Queues measure system resources and utilization. The queues part of performance is represented by Performance Monitor objects and counters and other sources of information. Performance Monitor counters measure various aspects of performance such as transfer rates for disks or the processor time consumed. SQL Server object counters are exposed to Performance Monitor using the dynamic management view (DMV) sys.dm_os_performance_counters. Thus, Performance Monitor counters show performance from a resource point of view.
Here’s some more MicroSpeak on the topic.
In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.
Resources:
Paul Randal’s Pluralsight Course
Wait statistics, or please tell me where it hurts