sp_waitstats
version: 1.0.0.1
last updated: 13 April 2005
platform: MSDE 2000 and SQL Server 2000 running Windows NT 4.0 and higher
Description:
Analyze SQL Server 2000 resource utilization using wait states. This stored procedure provides an overview of the resource utilization of your SQL Server instance, based on the wait statistics information captured using: DBCC SQLPERF(WAITSTATS). The stored procedure aggregates the wait times per wait type (resource).
When a UMS worker needs a resource which is not available, the worker is placed on the resource waiter list; when the resource is available, it gets signaled and gets placed on the runnable list; the worker stays on the runnable list until the worker is at the top of the list so it can be switched in and continue execution.
This implicitly means there are 2 distinctly different wait time; the first one is waiting for the resource to become available, the second one waiting to get switched in again to continue execution. The following diagram depicts these two different wait times. At T0 the wait state is entered and the worker is placed on the resource waiter list; at T1 the resource is available so the worker get signaled and is placed on the runnable list; at T2 the worker is at the top of the runnable list and is being switched in so it can continue execution.
DBCC SQLPERF(WAITSTATS) reports two wait times: [Wait Time] and [Signal Wait Time]
[Wait Time] is the total wait time between the time that the worker starts waiting on the resource (T0) and the time the workers gets scheduled in (T2).
[Signal Wait Time] is the time between the time that the worker got signaled (T1) that the resource is available and the time it got scheduled in (T2)
This means that you need to calculate the wait time on the resource by subtracting [Wait Time] from [Signal Wait Time].
Note: For more information on UMS see Inside the SQL Server 2000 User Mode Scheduler
Columns returned by sp_waitstats:
| Column name | Description |
requests | [requests] represents the number of times a worker requested a resource which was not immediately available and therefore was placed resource waiter list. |
[wait type] identifies the type of resource that was waited on. Note: In some cases a wait type represents a collection of resources. | |
total wait time | [total wait time] represents the total cumulative time that workers were waiting on a resource type (identified through its wait type) and waiting to get switched in (T2 - T0) |
resource wait time | [resource wait time] represents the total cumulative time that workers were waiting on a resource (identified through its wait type) to become available (T1 - T0) |
signal wait time | [signal wait time], represents the total cumulative time that workers were waiting to get switched in, after being signaled that the resource the worker was waiting for became available (T2 - T1) |
%total wait time | %total wait time, provides the relative wait time % of a wait type as a factor of the total wait time (T2 - T0) |
%resource wait time | %resource wait time, provides the relative wait time % of a wait type as a factor of the resource wait time (T1 - T0) |
%signal wait time | %signal wait time, provides the relative wait time % of a wait type as a factor of the signal wait time (T2 - T1)
|
avg total wait time | [avg total wait time] represents the average total wait time (T2 - T0) for a wait type, calculated as [total wait time] / [requests] |
avg resource wait time | [avg resource wait time] represents average resource wait time (T1 - T0) for a wait type, calculated as [resource wait time] / [requests] |
avg signal wait time | [avg signal wait time] represents average signal wait time (T2 - T1) for a wait type, calculated as [signal wait time] / [requests] |
Example:
exec sp_waitstats
exec sp_waitstats @orderby = 'total'
exec sp_waitstats @orderby = 'resource'
exec sp_waitstats @orderby = 'signal'