SQL Server -Find Blocking

Problem: Many a times DBA/Developer needs to identify the blocking in SQL server. Following queries will help you out.

Solution:

SELECT [owt].[session_id],
[owt].[exec_context_id],
[owt].[wait_duration_ms],
[owt].[wait_type],
[owt].[blocking_session_id],
owt.blocking_exec_context_id,
[owt].[resource_description],
CASE [owt].[wait_type]
WHEN N’CXPACKET’ THEN RIGHT ([owt].[resource_description],
Charindex (N’=’,
Reverse ([owt].[resource_description]))  1)
ELSE NULL
END AS [Node ID],
[es].[program_name],
[est].text,
[er].[database_id],
[eqp].[query_plan],
[er].[cpu_time]
FROM   sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es]
ON [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er]
ON [es].[session_id] = [er].[session_id]
OUTER apply sys.Dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER apply sys.Dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE  [es].[is_user_process] = 1
AND es.session_id >= 50
–and   [owt].[blocking_session_id] is null
–and owt.blocking_exec_context_id is null
ORDER  BY [owt].[session_id],
[owt].[exec_context_id];

go

SELECT TOP 10 Object_name(qt.objectid)                 AS ‘SP Name’,
Substring(qt.text, ( qs.statement_start_offset / 2 ) + 1,
(
( CASE qs.statement_end_offset
WHEN 1 THEN Datalength(qt.text)
ELSE qs.statement_end_offset
END

qs.statement_start_offset ) / 2 ) + 1) AS statement_text,
qp.query_plan,
qs.percent_complete
FROM   sys.dm_exec_requests AS qs
CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt
OUTER apply sys.Dm_exec_query_plan(qs.plan_handle) qp
WHERE  qt.dbid = Db_id() — Filter by current database

Leave a Reply

Your email address will not be published. Required fields are marked *