Tuesday, February 18, 2014

Identifying the root blocker and the blocking chain

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.

1 comment:

  1. Via LinkedIn
    For the DB2 LUW equivalent, please refer to the entry for Transaction Log full? at http://www.sustainablesoftware.net/downloads.htm - this shows the logic to report all lock chains created by the application holding the oldest uncommitted transaction.
    By Alex Levy

    ReplyDelete

How to fix cardinality estimation anomalies [Video]

Use the link mentioned below to watch the presentation that I delivered for PASS DBA Virtual Chapter about Filtered Statistics.  http:...