Joining sys.dm_os_waiting_tasks with sys.dm_tran_locks doesn’t work in SQL Server 2012 (RTM)

Last week, I was helping a developer to debug a lock information query that involves a join between DMVs sys.dm_os_waiting_tasks and sys.dm_tran_locks. I created two sessions. In session 1, simulated a lock by starting a transaction (in READCOMMITED isolation level) without committing and in the session 2, issued a SELECT query in the same table from the session 1 that has been blocked by the query in session 1. Then I executed the following query for lock information.








OBJECT_NAME(p.object_id) AS ObjectName FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address

INNER JOIN sys.partitions p

ON p.hobt_id = resource_associated_entity_id;

However the query doesn’t return the expected results. I queried the DMVs individually and found that the values in lock_owner_address column and resource_address column are different. I repeated the same test in another server and got the expected result! Then I checked the versions between the servers and found that the developer was working in SQL Server 2012 (RTM) and my second test was in SQL Server 2012 SP1. So this is a bug in the RTM version.

I searched the Microsoft connect website and found the below connect item by Kalen Delaney.

So I requested the system admin to install SP1 in the RTM version and issue sorted out.


Leave a comment

Filed under DMV, Internals

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s