Tracking down blocking....

Question

Every so often we experience extensive blocking  ( one spid blocks 10 to 30 others). We have looked at sql profiler traces near the time of the blocking but can't tell from them what caused the issue.   What suggestions can you offer to track down the real culprits?

Sql 2008

TIA,

Barkgindog

Answer 1

You will wan to look in the sys.dm_tran_locks DMV.  Check out this post by Glean Berry: http://www.sqlservercentral.com/blogs/glennberry/2010/04/27/a-dmv-a-day-_1320_-day-27/

SELECT tl.resource_type, tl.resource_database_id,
       tl.resource_associated_entity_id, tl.request_mode,
       tl.request_session_id, wt.blocking_session_id, 
       wt.wait_type, wt.wait_duration_ms
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC;

Answer 2

-- Detailed blocking information with query information
SELECT
owt.session_id AS waiting_session_id,
    owt.blocking_session_id,
DB_NAME(tls.resource_database_id) AS database_name,
    (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
(CASE WHEN ers.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE ers.statement_end_offset
END
- ers.statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
CASE WHEN owt.blocking_session_id > 0 
THEN (
SELECT
est.[text] FROM sys.sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
WHERE sp.spid = owt.blocking_session_id)
ELSE
NULL
END AS blocking_query_text,
    (CASE tls.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)
FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

END
) AS object_name,
owt.wait_duration_ms,
owt.waiting_task_address,
owt.wait_type,
tls.resource_associated_entity_id,
tls.resource_description AS local_resource_description,
tls.resource_type,
tls.request_mode,
tls.request_type,
tls.request_session_id,
owt.resource_description AS blocking_resource_description,
qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/