sys.dm_os_workers (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a row for every worker in the system.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_workers.

Column nameData typeDescription
worker_addressvarbinary(8)Memory address of the worker.
statusintInternal use only.
is_preemptivebit1 = Worker is running with preemptive scheduling. Any worker that is running external code is run under preemptive scheduling.
is_fiberbit1 = Worker is running with lightweight pooling. For more information, see sp_configure (Transact-SQL).
is_sickbit1 = Worker is stuck trying to obtain a spin lock. If this bit is set, this might indicate a problem with contention on a frequently accessed object.
is_in_cc_exceptionbit1 = Worker is currently handling a non-SQL Server exception.
is_fatal_exceptionbitSpecifies whether this worker received a fatal exception.
is_inside_catchbit1 = Worker is currently handling an exception.
is_in_polling_io_completion_routinebit1 = Worker is currently running an I/O completion routine for a pending I/O. For more information, see sys.dm_io_pending_io_requests (Transact-SQL).
context_switch_countintNumber of scheduler context switches that are performed by this worker.
pending_io_countintNumber of physical I/Os that are performed by this worker.
pending_io_byte_countbigintTotal number of bytes for all pending physical I/Os for this worker.
pending_io_byte_averageintAverage number of bytes for physical I/Os for this worker.
wait_started_ms_ticksintPoint in time, in ms_ticks, when this worker entered the SUSPENDED state. Subtracting this value from ms_ticks in sys.dm_os_sys_info returns the number of milliseconds that the worker has been waiting.
wait_resumed_ms_ticksintPoint in time, in ms_ticks, when this worker entered the RUNNABLE state. Subtracting this value from ms_ticks in sys.dm_os_sys_info returns the number of milliseconds that the worker has been in the runnable queue.
task_bound_ms_ticksbigintPoint in time, in ms_ticks, when a task is bound to this worker.
worker_created_ms_ticksbigintPoint in time, in ms_ticks, when a worker is created.
exception_numintError number of the last exception that this worker encountered.
exception_severityintSeverity of the last exception that this worker encountered.
exception_addressvarbinary(8)Code address that threw the exception
affinitybigintThe thread affinity of the worker. Matches the affinity of the thread in sys.dm_os_threads (Transact-SQL).
statenvarchar(60)Worker state. Can be one of the following values:

INIT = Worker is currently being initialized.

RUNNING = Worker is currently running either nonpreemptively or preemptively.

RUNNABLE = The worker is ready to run on the scheduler.

SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.
start_quantumbigintTime, in milliseconds, at the start of the current run of this worker.
end_quantumbigintTime, in milliseconds, at the end of the current run of this worker.
last_wait_typenvarchar(60)Type of last wait. For a list of wait types, see sys.dm_os_wait_stats (Transact-SQL).
return_codeintReturn value from last wait. Can be one of the following values:

0 =SUCCESS

3 = DEADLOCK

4 = PREMATURE_WAKEUP

258 = TIMEOUT
quantum_usedbigintInternal use only.
max_quantumbigintInternal use only.
boost_countintInternal use only.
tasks_processed_countintNumber of tasks that this worker processed.
fiber_addressvarbinary(8)Memory address of the fiber with which this worker is associated.

NULL = SQL Server is not configured for lightweight pooling.
task_addressvarbinary(8)Memory address of the current task. For more information, see sys.dm_os_tasks (Transact-SQL).
memory_object_addressvarbinary(8)Memory address of the worker memory object. For more information, see sys.dm_os_memory_objects (Transact-SQL).
thread_addressvarbinary(8)Memory address of the thread associated with this worker. For more information, see sys.dm_os_threads (Transact-SQL).
signal_worker_addressvarbinary(8)Memory address of the worker that last signaled this object. For more information, see sys.dm_os_workers.
scheduler_addressvarbinary(8)Memory address of the scheduler. For more information, see sys.dm_os_schedulers (Transact-SQL).
processor_groupsmallintStores the processor group ID that is assigned to this thread.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

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.

Requires VIEW SERVER STATE permission on the server.

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.

Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show: