Export (0) Print
Expand All
Expand Minimize

sys.dm_os_waiting_tasks (Transact-SQL)

Returns information about the wait queue of tasks that are waiting on some resource.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type

Description

waiting_task_address

varbinary(8)

Address of the waiting task.

session_id

smallint

ID of the session associated with the task.

exec_context_id

int

ID of the execution context associated with the task.

wait_duration_ms

int

Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time.

wait_type

nvarchar(60)

Name of the wait type.

resource_address

varbinary(8)

Address of the resource for which the task is waiting.

blocking_task_address

varbinary(8)

Task that is currently holding this resource

blocking_session_id

smallint

ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

blocking_exec_context_id

int

ID of the execution context of the blocking task.

resource_description

nvarchar(1024)

Description of the resource that is being consumed. For more information, see the list below.

The resource_description column has the following possible values.

Thread-pool resource owner:

  • threadpool id=scheduler<hex-address>

Parallel query resource owner:

  • exchangeEvent id={Port|Pipe}<hex-address> WaitType=<exchange-wait-type> nodeId=<exchange-node-id>

Exchange-wait-type:

  • e_waitNone

  • e_waitPipeNewRow

  • e_waitPipeGetRow

  • e_waitSynchronizeConsumerOpen

  • e_waitPortOpen

  • e_waitPortClose

  • e_waitRange

Lock resource owner:

  • <type-specific-description> id=lock<lock-hex-address> mode=<mode> associatedObjectId=<associated-obj-id>

    <type-specific-description> can be:

    • For DATABASE: databaselock subresource=<databaselock-subresource> dbid=<db-id>

    • For FILE: filelock fileid=<file-id> subresource=<filelock-subresource> dbid=<db-id>

    • For OBJECT: objectlock lockPartition=<lock-partition-id> objid=<obj-id> subresource=<objectlock-subresource> dbid=<db-id>

    • For PAGE: pagelock fileid=<file-id> pageid=<page-id> dbid=<db-id> subresource=<pagelock-subresource>

    • For Key: keylock hobtid=<hobt-id> dbid=<db-id>

    • For EXTENT: extentlock fileid=<file-id> pageid=<page-id> dbid=<db-id>

    • For RID: ridlock fileid=<file-id> pageid=<page-id> dbid=<db-id>

    • For APPLICATION: applicationlock hash=<hash> databasePrincipalId=<role-id> dbid=<db-id>

    • For METADATA: metadatalock subresource=<metadata-subresource> classid=<metadatalock-description> dbid=<db-id>

    • For HOBT: hobtlock hobtid=<hobt-id> subresource=<hobt-subresource> dbid=<db-id>

    • For ALLOCATION_UNIT: allocunitlock hobtid=<hobt-id> subresource=<alloc-unit-subresource> dbid=<db-id>

    <mode> can be:

    Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeI-N, RangeI-S, RangeI-U, RangeI-X, RangeX-, RangeX-U, RangeX-X

External resource owner:

  • External ExternalResource=<wait-type>

Generic resource owner:

  • TransactionMutex TransactionInfo Workspace=<workspace-id>

  • Mutex

  • CLRTaskJoin

  • CLRMonitorEvent

  • CLRRWLockEvent

  • resourceWait

Latch resource owner:

  • <db-id>:<file-id>:<page-in-file>

  • <GUID>

  • <latch-class> (<latch-address>)

Requires VIEW SERVER STATE permission on the server.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft