Find blocking sessions

Configurare noua (How To)

Situatie

Mai jos este o procedura care se ruleaza pe un server de sql si returneaza toate sesiunile ce sunt implicate in blocaje, furnizand cateva date despre acestea.

Solutie

create procedure FindBlockingSessions

as

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
JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id
JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id
RIGHT 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

Tip solutie

Permanent
Etichetare:

Voteaza

(10 din 14 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?