Queries to Identify the deadlocks:
Connect with the instance using admin:instancename and keep the session open. You will not be able to connect if SQL is down due to blocking/deadlocks.
Using 3rd query find the "Blocked By" and Kill with Session_ID
1st Query: To get the blockerSession, BlockedSession,Waiting_Time_ms,Waiting_Time_secs,Waiting_Time_mins,BlockerQuery,BlockedQuery,wait_type:
SELECT conn.session_id as blockerSession,
conn2.session_id as BlockedSession,
req.wait_time as Waiting_Time_ms,
cast((req.wait_time/1000.) as decimal(18,2)) as Waiting_Time_secs,
cast((req.wait_time/1000./60.) as decimal(18,2)) as Waiting_Time_mins,
t.text as BlockerQuery,
t2.text as BlockedQuery,
req.wait_type
FROM sys.dm_exec_requests as req
inner join sys.dm_exec_connections as conn on req.blocking_session_id=conn.session_id
inner join sys.dm_exec_connections as conn2 on req.session_id=conn2.session_id
cross apply sys.dm_exec_sql_text(conn.most_recent_sql_handle) as t
cross apply sys.dm_exec_sql_text(conn2.most_recent_sql_handle) as t2
2nd Query to get Waiting Session ID Waiting ecid Waiting RID Waiting pcid Waiting isolation level Waiting SQL Waiting Plan wait_type Blocking Session ID Blocking ecid Blocking RID Blocking pcid Blocking Session ID Blocking isolation level Blocking SQL Blocking Plan resource_description:
SELECT wt.session_id AS [Waiting Session ID],
wt.exec_context_id as [Waiting ecid],
wr.request_id as [Waiting RID],
wc.parent_connection_id as [Waiting pcid],
CASE ws.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unknown'
END AS [Waiting isolation level],
waiting_text.text AS [Waiting SQL],
waiting_plan.query_plan AS [Waiting Plan],
wt.wait_type,
bc.session_id AS [Blocking Session ID],
wt.blocking_exec_context_id AS [Blocking ecid],
br.request_id as [Blocking RID],
bc.parent_connection_id as [Blocking pcid],
bc.session_id AS [Blocking Session ID],
CASE bs.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unknown'
END AS [Blocking isolation level],
blocking_text.text AS [Blocking SQL],
blocking_plan.query_plan AS [Blocking Plan],
resource_description
FROM sys.dm_os_waiting_tasks wt
LEFT OUTER JOIN sys.dm_exec_sessions ws on wt.session_id = ws.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs on wt.blocking_session_id = bs.session_id
LEFT OUTER JOIN sys.dm_exec_connections wc on wt.session_id=wc.session_id
LEFT OUTER JOIN sys.dm_exec_connections bc on wt.blocking_session_id=bc.session_id
LEFT OUTER JOIN sys.dm_exec_requests wr on wt.session_id=wr.session_id
LEFT OUTER JOIN sys.dm_exec_requests br on wt.blocking_session_id=br.session_id
OUTER APPLY sys.dm_exec_sql_text(wc.most_recent_sql_handle) AS waiting_text
OUTER APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) AS blocking_text
OUTER APPLY sys.dm_exec_query_plan(wr.plan_handle) AS waiting_plan
OUTER APPLY sys.dm_exec_query_plan(br.plan_handle) AS blocking_plan
3rd Query to get CURRENT_TIME session_id status BlockedBy wait_type wait_resource Wait_sec cpu_time logical_reads reads writes Elaps_Sec statement_text command_text command login_name host_name program_name last_request_end_time login_time open_transaction_count:
SELECT getdate()as [CURRENT_TIME], s.session_id,
r.status,
r.blocking_session_id 'BlockedBy',
r.wait_type,
wait_resource,
r.wait_time / 1000.0 'Wait_sec',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000.0) 'Elaps_Sec',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
ORDER BY r.cpu_time,
r.status,
r.blocking_session_id,
s.session_id
4th query to the blocking tree.
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
No comments:
Post a Comment