Quickest Way to Identify Blocking Query and Resolution – Dirty Solution

Here is the script we used to identify the blocking query.

SELECT		db.name DBName,
			tl.request_session_id,
			wt.blocking_session_id,
			OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
			tl.resource_type,
			h1.TEXT AS RequestingText,
			h2.TEXT AS BlockingTest,
			tl.request_mode
FROM		sys.dm_tran_locks AS tl
INNER JOIN	sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN	sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN	sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN	sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN	sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY	sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY	sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

The query above returned us the following results:
 
In our case, we killed the blocking_session_id after carefully looking at the BlockingText; it was found to be not necessary at all. We killed the session using the following command:
KILL 52
As mentioned earlier, if you kill something important on your production server, there’s a great possibility that you’ll face some serious integrity issues, so I there’s no way I advise use this method. As the title goes, this is a dirty solution so you must utilize this only if you are confident.

Trovato qui.