SQL Server Lock Culprit Query?

Question

I found this from http://sqlserverdb.blogspot.com/2011/06/find-all-system-and-user-defined-error.html:

SELECT
    t.resource_type,
    t.resource_database_id,
    t.resource_associated_entity_id,
    t.request_mode,
    t.request_session_id,
    w.blocking_session_id
    FROM sys.dm_tran_locks as t
    INNER JOIN sys.dm_os_waiting_tasks AS w
    ON t.lock_owner_address =w.resource_address

And from there I am trying to derive the actual SQL statement that is coming from w.blocking_session_id but I can't seem to find the right bits. Can anyone point me to a clue?

I use this in one SSMS (SQL2008) query window:

begin transaction
update MyTable with (tablockx)
set MyTableColumn = MyTableColumn
where 1 = 0

And in another new window I executed this:

select * from MyTable

And SSMS shows it waiting 'forever' to perform the query, so it is the block victim. But I want to find out who the problem code actually is.

Thanks.

Answer 1

Given the sample queries you presented, the following complete example shows the blocking SQL using the sys.dm_os_waiting_tasks DMV in preference to the sys.dm_tran_locks DMV:

SELECT DISTINCT
  TheBlockingSQL.text AS [The blocking SQL]
FROM sys.dm_exec_connections AS Conns
  INNER JOIN sys.dm_exec_requests AS BlockedReqs
ON Conns.session_id = BlockedReqs.blocking_session_id
  INNER JOIN sys.dm_os_waiting_tasks AS w
ON BlockedReqs.session_id = w.session_id
  CROSS APPLY sys.dm_exec_sql_text(Conns.most_recent_sql_handle) AS TheBlockingSQL

Here is a screen capture of the output: