sys.dm_os_workers (Transact-SQL)

Remarks

If the worker state is RUNNING and the worker is running nonpreemptively, the worker address matches the active_worker_address in sys.dm_os_schedulers.

When a worker that is waiting on an event is signaled, the worker is placed at the head of the runnable queue. SQL Server allows for this to happen one thousand times in a row, after which the worker is placed at the end of the queue. Moving a worker to the end of the queue has some performance implications.

Permissions

Requires VIEW SERVER STATE permission on the server.

Examples

You can use the following query to find out how long a worker has been running in a SUSPENDED or RUNNABLE state.

SELECT 
    t1.session_id,
    CONVERT(varchar(10), t1.status) AS status,
    CONVERT(varchar(15), t1.command) AS command,
    CONVERT(varchar(10), t2.state) AS worker_state,
    w_suspended = 
      CASE t2.wait_started_ms_ticks
        WHEN 0 THEN 0
        ELSE 
          t3.ms_ticks - t2.wait_started_ms_ticks
      END,
    w_runnable = 
      CASE t2.wait_resumed_ms_ticks
        WHEN 0 THEN 0
        ELSE 
          t3.ms_ticks - t2.wait_resumed_ms_ticks
      END
  FROM sys.dm_exec_requests AS t1
  INNER JOIN sys.dm_os_workers AS t2
    ON t2.task_address = t1.task_address
  CROSS JOIN sys.dm_os_sys_info AS t3
  WHERE t1.scheduler_id IS NOT NULL;

Here is the result set.

session_id status command worker_state w_suspended w_runnable

---------- ---------- --------------- ------------ ----------- --------------------

4 background LAZY WRITER SUSPENDED 688 688

6 background LOCK MONITOR SUSPENDED 4657 4657

19 background BRKR TASK SUSPENDED 603820344 603820344

14 background BRKR EVENT HNDL SUSPENDED 63583641 63583641

51 running SELECT RUNNING 0 0

2 background RESOURCE MONITO RUNNING 0 603825954

3 background LAZY WRITER SUSPENDED 422 422

7 background SIGNAL HANDLER SUSPENDED 603820485 603820485

13 background TASK MANAGER SUSPENDED 603824704 603824704

18 background BRKR TASK SUSPENDED 603820407 603820407

9 background TRACE QUEUE TAS SUSPENDED 454 454

52 suspended SELECT SUSPENDED 35094 35094

1 background RESOURCE MONITO RUNNING 0 603825954

In the output, when w_runnable and w_suspended are equal, this represents the time that the worker is in the SUSPENDED state. Otherwise, w_runnable represents the time that is spent by the worker in the RUNNABLE state. In the output, session 52 is SUSPENDED for 35,094 milliseconds.

See Also

Reference

Concepts