缩小阻塞会话

Question

我使用不同的查询来查找阻塞查询。例如,

select
       ec.session_id,
       er.blocking_session_id,
       datediff(s, es.last_request_start_time, GETDATE()) as elapsed_time_sec,
       er.command,
       es.login_name,
       es.host_name,
       es.host_process_id,
       DB_NAME(er.database_id) as database_name,
       esql.text AS batch_text,
       ISNULL(CASE er.statement_end_offset + er.statement_start_offset
             WHEN 0 THEN esql.text 
             ELSE SUBSTRING(esql.text,
                          (er.statement_start_offset/2)+1, 
                          ((CASE er.statement_end_offset
                                       WHEN -1 THEN DATALENGTH(esql.text)
                                       ELSE er.statement_end_offset
                            END - er.statement_start_offset)/2) + 1)
       END, '')
       AS statement_text,
       eplan.query_plan,
       es.program_name,
       es.host_process_id,
       es.last_request_start_time,
       es.last_request_end_time,
       ec.num_reads as num_reads_connection,
       ec.num_writes as num_writes_connection,
       es.reads as reads_session,
       es.writes as writes_session,
       es.logical_reads,
       es.transaction_isolation_level,
       es.row_count,
       er.wait_type,
       er.wait_time
from 
       sys.dm_exec_connections ec
       left join sys.dm_exec_sessions es on ec.session_id = es.session_id
       left join sys.dm_exec_requests er on ec.session_id = er.session_id
       --left join sys.dm_exec_sessions es_blocking on es.session_id = er.blocking_session_id
       outer apply sys.dm_exec_sql_text(er.sql_handle) esql
       outer apply sys.dm_exec_query_plan(er.plan_handle) eplan
where es.status <> 'sleeping'
order by ec.session_id

在这里，我首先使用blocking_session_id不是null来检查行。然后从这个blocking_session_id中，发现它也会被其他人阻塞等等。所以，假设我有，

| -------------------- |
| Spid | Blokcing_Spid |
| -------------------- |
| 1 | 2 |
| 2 | 3 | 
| 3 | 4 | 
| 4 | NULL | 

这里4是罪魁祸首。我怎样才能迅速找到罪魁祸首？

Answer 1

检查sp_whoisactive由亚当马查尼- http://whoisactive.com/

它有@find_block_leaders参数。