4.1. SQL Server Waits
Whenever a task in SQL Server has to wait for something before it can continue, information on the reason for the wait is tracked by SQL Server and can be viewed through Dynamic Management Views (DMVs). Aggregating this data across all connections will give you a performance profile for SQL Server and tracking it for a particular connection will allow you to see what the bottleneck for a specific workload is.
All waits in SQL Server are categorized into wait types and can be grouped into three areas:
Resource waits: Occur when the requested resource is unavailable.
Queue waits: Occur when a worker is idle, waiting for work.
External waits: Occur when waiting for an external event.
Resource waits, which include I/O, locking, and memory, tend to be the most common and provide the most actionable information and so receive the most attention in this chapter.
Get Professional SQL Server® 2005 Performance Tuning now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
