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:
exec sp_waitstats @orderby = 'total'
exec sp_waitstats @orderby = 'resource'
exec sp_waitstats @orderby = 'signal'
Questions or problems regarding this web site should be directed to Web Master.