cat figuring-out-which-session-is-blocking-a-query-on-microsoft-sql-server-query-suspended.md
Figuring out which session is blocking a query on Microsoft SQL Server (query suspended)
I was recently debugging a case where a customer's installation was hanging due to a database lock. The issue turned out to be that the database was not set to a default transaction level of READ COMMITTED SNAPSHOT, which the product expects. Regardless, while troubleshooting the issue, I stumbled upon a very useful SQL query that I needed to save somewhere, so here goes.
The following query lists which SQL session is blocked by which other session:
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
ORDER BY tl.request_session_id
GO...and by following the tree (X blocked by Y, blocked by Z, etc..) one can see which session is responsible for the mess.
Other interesting queries:
| Query | Description |
|---|---|
sp_who2 |
Shows sessions connected to the database, how much time they've spent, as well as several other stats |
select cmd,* from sys.sysprocesseswhere blocked > 0 |
Shows currently blocked queries |
Comments