DBCC SQLPERF(WAITSTATS) The result set: Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ --------------
MISCELLANEOUS 0.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 0.0 0.0 0.0
LCK_M_U 0.0 0.0 0.0
LCK_M_X 0.0 0.0 0.0
LCK_M_IS 0.0 0.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 0.0 0.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 0.0 0.0 0.0
IO_COMPLETION 0.0 0.0 0.0
ASYNC_IO_COMPLETION 0.0 0.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 0.0 0.0 0.0
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 0.0 0.0 0.0
ASYNC_DISKPOOL_LOCK 0.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 0.0 0.0 0.0
LOGBUFFER 0.0 0.0 0.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 0.0 0.0 0.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 0.0 0.0 0.0
CXPACKET 0.0 0.0 0.0
PAGESUPP 0.0 0.0 0.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 0.0 0.0 0.0
CURSOR 0.0 0.0 0.0
EXECSYNC 0.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 0.0 0.0 0.0
LATCH_UP 0.0 0.0 0.0
LATCH_EX 0.0 0.0 0.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 0.0 0.0 0.0
PAGELATCH_SH 0.0 0.0 0.0
PAGELATCH_UP 0.0 0.0 0.0
PAGELATCH_EX 0.0 0.0 0.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 0.0 0.0 0.0
PAGEIOLATCH_UP 0.0 0.0 0.0
PAGEIOLATCH_EX 0.0 0.0 0.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 0.0 0.0 0.0
Total 0.0 0.0 0.0
Description of the columns: | Statistic | Description | | Wait Type | The type of resource that was waited on | | Requests | Requests represents the number of times the wait state for a given wait type was entered; in other words resource indicated by wait type X was requested and not available at the time of the request as a result the session was
Not number of time resource got used! | | Wait Time | Wait Time represents the total time waiting on resource plus the time until the worker thread gets scheduled in so it can use the resource | | Signal Wait Time | Signal Wait Time represents the time in “runnable” queue prior to continuation of execution |
Further explanation of wait times: When a worker needs a resource which is not available, the worker thread is placed on the resource waiter list; when the resource is available, it gets signaled and gets placed on the runnable list; it stays on the runnable list until the worker is at the top of the list again in which case it will continue execution, after being switched in. 
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 substracting [Wait Time] from [Signal Wait Time]. Note: For more information on UMS see Inside the SQL Server 2000 User Mode Scheduler The table structure returned, has the following shape: create table #waitstats
(
[Wait Type] nvarchar(32) not null,
[Requests] float not null,
[Wait Time] float not null,
[Signal Wait Time] float not null
) To populate the table use: insert into #waitstats exec('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')
select * from #waitstats DBCC SQLPERF(WAITSTATS, CLEAR) resets the counters, this is a service/instance wide reset, not per session! NOTE: For SQL Server 2005, please use the sys.dm_os_wait_stats dynamic management view instead of DBCC SQLPERF(waitstats), also the sys.dm_os_waiting_tasks table provides information on all currently waiting tasks in the system. | 
