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.

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:...