Validated against: SQL Server 2005, 2008, 2008 R2.
Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty much all the information you will ever need. If any suggestions to improve it, please be sure to let me know.
;WITH request_mode ( request_mode, [description] ) AS ( SELECT 'NULL', 'No access s granted to the resource. Serves as a placeholder.' UNION ALL SELECT 'Sch-S', 'Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.' UNION ALL SELECT 'Sch-M', 'Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.' UNION ALL SELECT 'S', 'Shared. The holding session is granted shared access to the resource.' UNION ALL SELECT 'U', 'Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.' UNION ALL SELECT 'X', 'Exclusive. The holding session is granted exclusive access to the resource.' UNION ALL SELECT 'IS', 'Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.' UNION ALL SELECT 'IU', 'Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.' UNION ALL SELECT 'IX', 'Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.' UNION ALL SELECT 'SIU', 'Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.' UNION ALL SELECT 'SIX', 'Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.' UNION ALL SELECT 'UIX', 'Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.' UNION ALL SELECT 'BU', 'Bulk Update. Used by bulk operations.' UNION ALL SELECT 'RangeS_S', 'Shared Key-Range and Shared Resource lock. Indicates serializable range scan.' UNION ALL SELECT 'RangeS_U', 'Shared Key-Range and Update Resource lock. Indicates serializable update scan.' UNION ALL SELECT 'RangeI_N', 'Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.' UNION ALL SELECT 'RangeI_S', 'Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.' UNION ALL SELECT 'RangeI_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and U locks.' UNION ALL SELECT 'RangeI_X', 'Key-Range Conversion lock created by an overlap of RangeI_N and X locks.' UNION ALL SELECT 'RangeX_S', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.' UNION ALL SELECT 'RangeX_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.' UNION ALL SELECT 'RangeX_X', 'Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.' ) SELECT d.[name] AS [DatabaseName], wt.[session_id] AS [Waiting SessionId], CAST(wt.[wait_duration_ms] / 1000. AS DECIMAL(38, 1)) AS [Waiting Duration (Secs)], CAST((wt.[wait_duration_ms] / 1000.)/60. AS DECIMAL(38, 1)) AS [Waiting Duration (Mins)], wt.[wait_type] AS [Waiting Type], wt.[resource_description] AS [Waiting ResourceDescription], tl.[request_mode] AS [Waiting RequestMode], rm.[description] AS [Waiting RequestModeDescription], tl.[request_type] AS [Waiting RequestType], tl.[request_status] AS [Waiting RequestType], ( SELECT SUBSTRING(ST.text, ( ER.statement_start_offset / 2 ) + 1, ( ( CASE ER.statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE ER.statement_end_offset END - ER.statement_start_offset ) / 2 ) + 1) FROM sys.dm_exec_requests AS ER WITH ( NOLOCK ) CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST WHERE ER.session_id = tl.request_session_id ) AS [Waiting SQL], wt.[blocking_session_id] AS [Blocking SessionId], CASE WHEN wt.blocking_session_id > 0 THEN ( SELECT ST2.text FROM sys.sysprocesses AS SP WITH ( NOLOCK ) CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2 WHERE SP.spid = wt.blocking_session_id ) ELSE NULL END AS [Blocking SQL], CASE WHEN UPPER(tl.[resource_type]) = 'OBJECT' THEN ( SELECT [name] FROM [sys].[objects] o1 WITH ( NOLOCK ) WHERE [o1].object_id = tl.[resource_associated_entity_id] ) ELSE ( SELECT [name] FROM [sys].[objects] o1 WITH ( NOLOCK ) WHERE [o1].object_id = p.[OBJECT_ID] ) END AS [Object Name], p.[index_id], i.[name] AS index_name FROM sys.dm_os_waiting_tasks AS wt WITH ( NOLOCK ) JOIN sys.dm_tran_locks AS tl WITH ( NOLOCK ) ON wt.resource_address = tl.lock_owner_address JOIN sys.databases d WITH ( NOLOCK) ON tl.[resource_database_id] = d.[database_id] LEFT JOIN [sys].[partitions] AS p WITH ( NOLOCK ) ON tl.[resource_associated_entity_id] = p.[hobt_id] LEFT JOIN [sys].[indexes] AS i WITH ( NOLOCK ) ON p.[OBJECT_ID] = i.[OBJECT_ID] AND p.[index_id] = i.[index_id] LEFT JOIN request_mode rm WITH (NOLOCK) ON tl.[request_mode] = rm.[request_mode] WHERE wt.[blocking_session_id] IS NOT NULL AND wt.[wait_duration_ms] >= 5000/*5 seconds or more*/;