Figure out the Blocking issue

,

Please run the query which is showing the Host IP , BlockingSessionId, BlockingUser, BlockingSQL as a text , WhyBlocked, BlockedSQL and Database name. Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. The below query will help out to get the root cause why it is Blocked

 

SELECT client_net_address as HOSTIP,Blocking.session_id as BlockingSessionId , Sess.login_name AS BlockingUser , BlockingSQL.text AS BlockingSQL , Waits.wait_type WhyBlocked 
, Blocked.session_id AS BlockedSessionId , USER_NAME(Blocked.user_id) AS BlockedUser , BlockedSQL.text AS BlockedSQL , DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id 
INNER JOIN sys.dm_os_waiting_tasks AS Waits  ON Blocked.session_id = Waits.session_id RIGHT OUTER JOIN sys.dm_exec_sessions Sess  ON Blocking.session_id = sess.session_id 
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL ORDER BY BlockingSessionId, BlockedSessionId

Rate

3.88 (8)

Share

Share

Rate

3.88 (8)