SQL Server Performance

Why does a TASK_STATE of RUNNING sometime show a WAIT_TYPE?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Mar 24, 2010.

  1. DBADave New Member

    When joining sys.dm_os_tasks with sys.dm_os_waiting_tasks ON session_id AND exec_context_id I sometimes see a TASK_STATE of RUNNING with a corresponding value for WAIT_TYPE and WAIT_DURATION_MS. How could a task with a state of RUNNING be also waiting on a resource? I thought I should only see waiting for task states of RUNNABLE, PENDING and SUSPENDED, but not RUNNING. sysprocesses does not show this type of behavior. It has been suggested to me that perhaps the WAIT_TYPE is actually the last wait type. I'm not sure that is correct based upon BOL's definitions of the above DMVs.

    Thanks, Dave
  2. MohammedU New Member

    Here is an example for this type of scenario I can think of but not tested....
    When a query executed by user, and the optimizer chooses to use the parallalism to process it...
    When the query distributed to multiple processors then one processor may finish earlier than the other but has to wait for other processors to finish...
    you may see the wait_type...
  3. satya Moderator

  4. DBADave New Member

    I spoke with Microsoft regarding this issue and, after some research on their end, here is why waiting can occur when TASK_STATE is running.
    · When TASK_STATE is RUNNING and WAIT_TYPE is a non-null value and WAIT_DURATION_MS = 0, then WAIT_TYPE is the last wait type
    · When TASK_STATE is RUNNING and WAIT_TYPE is a non-null value and WAIT_DURATION_MS > 0, then WAIT_TYPE is the current wait type, which indicates SQL Server is waiting on an external resource such as a linked server, OLEDB and xp_cmdshell
    · When TASK_STATE is not RUNNING and WAIT_TYPE is a non-null value, then WAIT_TYPE is the current wait type, which indicates SQL Server is waiting on an internal resource such as network, disk, memory, operating system (SOS_Scheduler_Yield)
    Unfortunately this is not documented by Microsoft.

Share This Page