0

I am using different queries to find the blocking query. For example,

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

Here I am first checking rows with blocking_session_id is not null. Then from this blocking_session_id, finding it is also block by others and so on. So, let's say I have,

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

Here 4 is the culprit. How can I quickly find the culprit spids?

4

Check sp_whoisactive by Adam Machanic - http://whoisactive.com/
It has @find_block_leaders parameter.

1
  • super cool @NikitaSerbskiy – user960567 Nov 4 '20 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.