As a DBA, identifying blockings and waitings is one of the primary tasks. I searched in the Google but did not find a useful script. So I came up with one and thought to share it with others.
;WITH requests (session_id, start_time, status, blocking_session_id,
database_name,
command, sql_text)
AS (SELECT session_id,
start_time,
status,
blocking_session_id,
Db_name(database_id),
command,
sql_text = Cast(text AS VARCHAR(max))
FROM sys.dm_exec_requests WITH (nolock)
CROSS apply sys.Dm_exec_sql_text (sql_handle)
WHERE status <> 'Background'),
blocking (session_id, start_time, status, blocking_session_id, command,
sql_text, rownum, levelrow)
AS (SELECT r1.session_id,
r1.start_time,
r1.status,
r1.blocking_session_id,
r1.command,
r1.sql_text,
Row_number()
OVER (
ORDER BY r1.session_id),
0 AS LevelRow
FROM requests r1
INNER JOIN requests r2
ON r1.session_id = r2.blocking_session_id
WHERE r1.blocking_session_id = 0
UNION ALL
SELECT r3.session_id,
r3.start_time,
r3.status,
r3.blocking_session_id,
r3.command,
r3.sql_text,
b.rownum,
b.levelrow + 1
FROM requests r3
INNER JOIN blocking b
ON r3.blocking_session_id = b.session_id
WHERE r3.blocking_session_id > 0)
SELECT *
FROM blocking
ORDER BY rownum,
levelrow
Alternatively you can write the output of this to a table as a schedule job and the results can be analyzed later. This is really useful to identify any blocking patterns of the database server and fix them if they persist.
