Untitled
unknown
plain_text
a month ago
1.9 kB
4
Indexable
USE [master] ---root blocking spid select * from sys.sysprocesses where blocked > 0 GO Select spid,hostname,program_name,nt_username,loginame,cmd, t.text from sys.sysprocesses CROSS APPLY( select text from sys.dm_exec_sql_text(sql_handle))t Go ------Blocking detection with Root cause USE [master] GO Select DES.Session_ID As [Root Blocking Session ID], DER.Status As [Blocking Session Request Status], DES.Login_Time As [Blocking Session Login Time], DES.Login_Name As [Blocking Session Login Name], DES.Host_Name As [Blocking Session Host Name], Coalesce(DER.Start_Time,DES.Last_Request_Start_Time) As [Request Start Time], Case When DES.Last_Request_End_Time >= DES.Last_Request_Start_Time Then DES.Last_Request_End_Time Else Null End As [Request End Time], Substring(Text,DER.Statement_Start_Offset/2, Case When DER.Statement_End_Offset = -1 Then DataLength(Text) Else DER.Statement_End_Offset/2 End) As [Executing Command], Case When DER.Session_ID Is Null Then 'Blocking session does not have an open request and may be due to an uncommitted transaction.' When DER.Wait_Type Is Not Null Then 'Blocking session is currently experiencing a '+DER.Wait_Type+' wait.' When DER.Status = 'Runnable' Then 'Blocking session is currently waiting for CPU time.' When DER.Status = 'Suspended' Then 'Blocking session has been suspended by the scheduler.' Else 'Blocking session is currently in a '+DER.Status+' status.' End As [Blocking Notes] From Sys.DM_Exec_Sessions DES (READUNCOMMITTED) Left Join Sys.DM_Exec_Requests DER (READUNCOMMITTED) On DER.Session_ID = DES.Session_ID Outer Apply Sys.DM_Exec_Sql_Text(DER.Sql_Handle) Where DES.Session_ID In ( Select Blocking_Session_ID From Sys.DM_Exec_Requests (READUNCOMMITTED) Where Blocking_Session_ID <> 0 And Blocking_Session_ID Not In ( Select session_id From Sys.DM_Exec_Requests (READUNCOMMITTED) Where Blocking_Session_ID <> 0 ) )
Editor is loading...
Leave a Comment