What is Query Blockage

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work.

From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.

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.