T-SQL query to find deadlocks in SQL Server

Earlier, I wrote an article about using extended events to to find the deadlocks occurring on a SQL Server. Extended events will be a great help to track the deadlocks happened on the server for short period of time, especially on a production environment. However, In the development environment, I came across situations where the ongoing long duration deadlocks appearing when multiple developers trying to execute dml statements against a table. During such situations, I used a light weight T-SQL query to find deadlocks i.e, blocking and blocked session-ids of SQL connections. Based on the details returned by this statement, I was able to find the application or user which has executed the blocking session and helped me to kill the specific SQL connection. It also helped us to identify and fix the frequently blocking SQL statement.

Below is the query I have used to quickly find the deadlocks. This statement is based on the SYS.DM_EXEC_REQUESTS dynamic management view. In this statement, the column blocking_session_id gives you the session_id of the connection which is blocking and the column wait_type gives you the type of wait which caused the deadlock. After getting the blocking_session_id, you can use another dmv SYS.DM_EXEC_SESSIONS to get more details about the session or connection.

SELECT 
	session_id,
	start_time, 
	[status],
	command,
	blocking_session_id,
	wait_type,
	wait_time,
	open_transaction_count,
	transaction_id,
	total_elapsed_time,
	Definition = CAST(text AS VARCHAR(MAX))
FROM 
	SYS.DM_EXEC_REQUESTS
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
WHERE blocking_session_id != 0

Hope this simple query to find deadlocks will be helpful to you all.

Reference

• More about sys.dm_exec_requests dmv at Microsoft Docs.