September 29, 2011 at 6:25 am
I have written the following query to get details of any processes currently involved in blocking (either being blocked, or at the head of the blocking chain). I have then set up a job to execute this every minute and insert the results into a table for later analysis.
The logic is fairly simple: I am just returning any session that is currently blocked, or where there's another session being blocked by it.
What I am finding is that sometimes the process at the head of the blocking chain has not been captured (for a particular set of sessions there is no row where chain_head = 1). I have a feeling that it's due to the dm_exec_requests DMV being scanned twice (once in the main query and once in the subquery) and the rows are changing between these two accesses.
Is there any way to rewrite this query so the head blockers don't get missed? Or could the root cause be something else?
SELECT GETDATE() AS block_time,
s.session_id,
r.blocking_session_id AS blocked_by,
CASE WHEN ISNULL(blocking_session_id, 0) = 0 THEN 1 ELSE 0 END AS chain_head,
-- <more columns etc.>
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
LEFT JOIN sys.sysprocesses sp ON s.session_id = sp.spid
AND sp.ecid = 0
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id <> 0
OR EXISTS
(
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id = s.session_id
);
-----
JL
September 30, 2011 at 2:14 pm
There are cases where a blocking session may not fall under either of these criteria:
...
WHERE r.blocking_session_id <> 0
OR EXISTS
(
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id = s.session_id
);
By definition, the head of the blocking chain will not satisfy condition 1: r.blocking_session_id <> 0.
Also, as you say, the contents of sys.dm_exec_requests may have changed between the 2 reads.
I use the following to track blocking conditions, although it is also not without its own problems;
occasionally freezes up on me when the number of locks involved is large:
--Adapted from:
--http://www.simple-talk.com/sql/database-administration/investigating-transactions-using-dynamic-management-objects/
SELECT
DTL.[resource_type] AS [resource type] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [wait duration ms] ,
DOWT.[wait_type] AS [wait type] ,
DOWT.[session_id] AS [blocked session id] ,
DES_blocked.[login_name] AS [blocked_user] ,
LEFT (
SUBSTRING(dest_blocked.text, DER.statement_start_offset / 2,
(CASE WHEN DER.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE DER.statement_end_offset
END - DER.statement_start_offset ) / 2 ) , 500 )
AS [blocked_command] ,
DOWT.[blocking_session_id] AS [blocking session id] ,
DES_blocking.[login_name] AS [blocking user] ,
LEFT (DEST_blocking.[text], 500) AS [blocking command] ,
DOWT.resource_description
FROM
sys.dm_tran_locks DTL
INNER JOIN
sys.dm_os_waiting_tasks DOWT
ON
DTL.lock_owner_address = DOWT.resource_address
INNER JOIN
sys.[dm_exec_requests] DER
ON
DOWT.[session_id] = DER.[session_id]
INNER JOIN
sys.dm_exec_sessions DES_blocked
ON
DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN
sys.dm_exec_sessions DES_blocking
ON
DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN
sys.dm_exec_connections DEC
ON
DTL.[request_session_id] = DEC.[most_recent_session_id]
CROSS APPLY
sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking
CROSS APPLY
sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply