Better documentation for tasks waiting on resources
- by SQLOS Team
The sys.dm_os_waiting_tasks DMV contains a wealth of useful information about tasks waiting on a resource, but until now detailed information about the resource being consumed - sys.dm_os_waiting_tasks.resource_description - hasn't been documented, apart from a rather self-evident "Description of the resource that is being consumed."
Thanks to a recent Connect suggestion this column will get more information added. Here is a summary of the possible values that can appear in this column - Note this information is current for SQL Server 2008 R2 and Denali:
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 can be one of the following.• 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-S• 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>)
Further Information
Slava Oks's weblog: sys.dm_os_waiting_tasks.Informit.com: Identifying Blocking Using sys.dm_os_waiting_tasks - Ken Henderson
- Guy
