Note
Access to this page requires authorization. You can try signing in or .
Access to this page requires authorization. You can try .
sys.dm_os_tasks (Transact-SQL)
Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
SQL database in Microsoft Fabric
Returns one row for each task that is active in the instance of SQL Server. A task is the basic unit of execution in SQL Server. Examples of tasks include a query, a login, a logout, and system tasks like ghost cleanup activity, checkpoint activity, log writer, parallel redo activity. For more information about tasks, see the Thread and Task Architecture Guide.
Note
To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_tasks. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
| 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). |
| parent_task_address | varbinary(8) | Memory address of the task that is the parent of the object. |
| pdw_node_id | int | Applies to: Azure Synapse Analytics, Analytics Platform System (PDW) The identifier for the node that this distribution is on. |
Permissions
On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE 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 Configure the max degree of parallelism Server Configuration Option 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
See also
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Thread and Task Architecture Guide
Feedback
Was this page helpful?