SQL Server 2005 Waits and Queues

Really, really useful. Everywhere I go the same problems exist. SQL Server slow and no one knows why. It's a useful topic and this is a good part of it.

SQL Server 2005 Waits and Queues - SQL Server Best Practices Article, by Tom Davidson, 2005

It can be downloaded from http://bit.ly/aUh6S


"wait time" - the total time taken for a thread to go from Running, to being "put on the queue", or to being "queued, and then back to Running again.

"resource wait time" - the time a thread is waiting on the Waiter List with the state of Suspended.

"signal wait time" - time waiting for the processor once the resource has become available.

So wait time = resource wait time + signal wait time

When a thread has server time, and then becomes blocked, it is set as Suspended and then placed on the Waiter list.

When it receives a signal to say the resource is available, it is set to Runnable and it is place on the Runnable Queue. It will wait here until it is given to the Processor.

sys.dm_os_waiting_tasksDMV
Will show all threads that are currently suspended.