مشاركة عبر

  • مقالة

sys.dm_os_tasks (Transact-SQL)

Returns one row for each task that is active in the instance of SQL Server.

Column name

Data type

Description

task_address

varbinary(8)

Memory address of the object.

task_state

nvarchar(60)

State of the task. This can be one of the following:

PENDING: Waiting for a worker thread.

RUNNABLE: Runnable, but waiting to receive a quantum.

RUNNING: Currently running on the scheduler.

SUSPENDED: Has a worker, but is waiting for an event.

DONE: Completed.

SPINLOOP: Stuck in a spinlock.

context_switches_count

int

Number of scheduler context switches that this task has completed.

pending_io_count

int

Number of physical I/Os that are performed by this task.

pending_io_byte_count

bigint

Total byte count of I/Os that are performed by this task.

pending_io_byte_average

int

Average byte count of I/Os that are performed by this task.

scheduler_id

int

ID of the parent scheduler. This is a handle to the scheduler information for this task. For more information, see sys.dm_os_schedulers (Transact-SQL).

session_id

smallint

ID of the session that is associated with the task.

exec_context_id

int

Execution context ID that is associated with the task.

request_id

int

ID of the request of the task. For more information, see sys.dm_exec_requests (Transact-SQL).

worker_address

varbinary(8)

Memory address of the worker that is running the task.

NULL = Task is either waiting for a worker to be able to run, or the task has just finished running.

For more information, see sys.dm_os_workers (Transact-SQL).

host_address

varbinary(8)

Memory address of the host.

0 = Hosting was not used to create the task. This helps identify the host that was used to create this task.

For more information, see sys.dm_os_hosts (Transact-SQL).

Permission

Requires VIEW SERVER STATE permission on the server.

Examples

A. Monitoring parallel requests

For requests that are executed in parallel, you will see multiple rows for the same combination of (<session_id>, <request_id>). Use the following query to find the degree of parallelism for all active requests.

Note

A request_id is unique within a session.

SELECT
    task_address,
    task_state,
    context_switches_count,
    pending_io_count,
    pending_io_byte_count,
    pending_io_byte_average,
    scheduler_id,
    session_id,
    exec_context_id,
    request_id,
    worker_address,
    host_address
  FROM sys.dm_os_tasks
  ORDER BY session_id, request_id;

B. Associating session IDs with Windows threads

You can use the following query to associate a session ID value with a Windows thread ID. You can then monitor the performance of the thread in the Windows Performance Monitor. The following query does not return information for sessions that are sleeping.

SELECT STasks.session_id, SThreads.os_thread_id
  FROM sys.dm_os_tasks AS STasks
  INNER JOIN sys.dm_os_threads AS SThreads
    ON STasks.worker_address = SThreads.worker_address
  WHERE STasks.session_id IS NOT NULL
  ORDER BY STasks.session_id;
GO