THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLOS Team

A blog for members of the SQL Server SQLOS team to share information and get your feedback.

Better documentation for tasks waiting on resources

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

Published Wednesday, December 29, 2010 2:21 PM by SQLOS Team

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Thanks for filling in the gaps, but the Connect item was not "recent." I filed it two and a half years ago. Just some food for thought :-)

December 29, 2010 5:15 PM
 

SQLOS Team said:

Adam, ouch, fair point.. we overlooked that one. Let me know if you have any more like that.

December 29, 2010 5:39 PM
 

Marios Philippopoulos said:

I'm getting the following for resource_description:

objectlock lockPartition=0 objid=159092103 subresource=FULL dbid=11 id=lock34781ca80 mode=X associatedObjectId=159092103

I get database name from DB_NAME(11), but when I run either of the following while in that database I get NULL:

SELECT OBJECT_NAME(159092103);

SELECT OBJECT_NAME(159092103,11);

How can I get the object name? Also what is the difference between objid and associatedObjectId?

September 1, 2011 10:32 AM
 

SQLOS Team said:

Hi Marios, here are some comments from Jason in our HA team..

There are two questions here.

1. How can I get the object name?

It’s by design based on BOL not to show OBJECT_NAME depending on permission.

dm_os_waiting_tasks  : Requires VIEW SERVER STATE permission on the server.

OBJECT_NAME  : Returns NULL on error or if a caller does not have permission to view the object. If the target database has the AUTO_CLOSE option set to ON, the function will open the database. A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object.

It looks like there is no sufficient permission to see the object.

Repro can be by creating login ‘user1’ which has only VIEW SERVER STATE permission.

use testdb

go

Msg 916, Level 14, State 1, Line 0

The server principal "user1" is not able to access the database "testdb" under the current security context.

select * from sys.dm_os_waiting_tasks

where resource_description is not null

brick_id      waiting_task_address session_id    exec_context_id      wait_duration_ms     wait_type       resource_address     blocking_task_address      blocking_session_id  blocking_exec_context_id       resource_description

1      0x00000003FF02EF58   53     0      2307902       LCK_M_X       0x000000000C68BB20   NULL   52       NULL   objectlock lockPartition=0 objid=245575913 subresource=FULL dbid=5 id=lock3f3e28290 mode=X associatedObjectId=245575913

SELECT DB_NAME(5)

(No column name)

Testdb    same as your description

SELECT OBJECT_NAME(245575913)

(No column name)

NULL      same as your description

With the other account (the owner of the database), OBJECT_NAME works fine as below.

(No column name)

T               correct result with proper permission

The answer is

- Use user with proper permission to see object.

- Try to create own database and make threads waiting for the other over the database. Since you have full permission for the database and object , you can see also OBJECT_NAME properly. Use different login without adding further permission to see the null value from OBJECT_NAME.

2. what is the difference between objid and associatedObjectId?

The associatedObjectId is the id of the object that this lock resource is associated with.

In this case, the lock resource is object so that they should be same.

If the lock resource is other than object, it can be different. For example KEY and HOBT return its HOBT id which can be different from object id.

October 18, 2011 8:09 PM
 

Intra-Query Parallel Deadlock | Krishnakumar's SQL Server Blog said:

November 25, 2014 8:11 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement