February 4, 2014 at 9:14 am
I have this query to find blocking:
SELECT DISTINCT sp.spid SPID, sp.blocked BlockingSPID, DB_NAME(tl.resource_database_id) DatabaseName, es.Status
, (SELECT Text FROM sys.dm_exec_sql_text(sp.sql_handle)) SQLText
, es.HOST_NAME HostName, es.Login_Name, es.PROGRAM_NAME ProgramName, tl.resource_type LockType, tl.request_mode RequestMode
, CASE WHEN tl.resource_type = 'OBJECT'
THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
WHEN tl.resource_type IN ('KEY', 'PAGE', 'RID')
THEN (
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions ps1
WHERE ps1.hobt_id = tl.resource_associated_entity_id
)
END AS ObjectName
, tl.request_status LockStatus, er.wait_type WaitType
, CONVERT(float, ROUND((ISNULL(er.total_elapsed_time, 0.0) / 1000.00), 0)) Blocked_Time_in_seconds
INTO #Blocking
FROM sys.dm_exec_sessions es
JOIN sys.sysprocesses sp ON sp.spid = es.session_id
LEFT JOIN sys.dm_exec_connections ec ON ec.session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er ON er.session_id = es.session_id
LEFT JOIN sys.dm_tran_locks tl ON tl.request_session_id = es.session_id
WHERE tl.resource_type = 'OBJECT'
ORDER BY BlockingSPID, sp.SPID
;
WITH Blocking (SPID, BlockingSPID, DatabaseName, Status, SQLText, HostName, Login_Name, ProgramName, LockType, RequestMode
, ObjectName, LockStatus, WaitType, Blocked_Time_in_Seconds, RowNo, LevelRow)
AS
(
SELECT b.SPID, b.BlockingSPID, b.DatabaseName, b.Status, b.SQLText, b.HostName, b.Login_Name, b.ProgramName, b.LockType, b.RequestMode
, b.ObjectName, b.LockStatus, b.WaitType, b.Blocked_Time_in_Seconds
, ROW_NUMBER() OVER (ORDER BY s.SPID), 0 AS LevelRow
FROM #Blocking b
JOIN #Blocking b1 ON b.SPID = b1.BlockingSPID
WHERE b.BlockingSPID = 0
UNION ALL
SELECT b2.SPID, b2.BlockingSPID, b2.DatabaseName, b2.Status, b2.SQLText, b2.HostName, b2.Login_Name, b2.ProgramName, b2.LockType, b2.RequestMode
, b2.ObjectName, b2.LockStatus, b2.WaitType, b2.Blocked_Time_in_Seconds, d.RowNo, d.LevelRow + 1
FROM #Blocking b2
JOIN Blocking d ON b2.BlockingSPID = d.SPID
WHERE b2.BlockingSPID > 0
)
SELECT *
FROM Blocking a
ORDER BY RowNo, LevelRow
DROP TABLE #Blocking
I recently added the ", tl.resource_database_id" on line 5 to get the database for the objects. After that it is susceptible to getting blocked in the Tempdb. Any idea why or how to fix it?
There is an exception to every rule, except this one...
February 4, 2014 at 9:46 am
I can add that it is not self-blocking.
There is an exception to every rule, except this one...
February 4, 2014 at 9:56 am
And I messed up an identifier...
, ROW_NUMBER() OVER (ORDER BY b.SPID), 0 AS LevelRow
Same locking issue still
There is an exception to every rule, except this one...
February 5, 2014 at 5:45 am
Not sure without seeing what is being blocked. One recommendation, get rid of the drop table statement entirely. Let SQL Server clean it up on its own. Not saying that will help the blocking issue, but it won't hurt.
What about using extended events to capture the blocking? You can set a block threshold and capture the stuff automatically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply