DaveWentzel.com            All Things Data

Blocking

 

There are blocking "patterns" that seem to recur and understanding the patterns can help you get to root cause. 
 
1)A single long wait.  Probably won't affect overall server throughput, but it will affect the query(s) being blocked.  In dm_os_waiting_tasks look for wait_time_ms over a certain threshold. 
 
2)Many waits for one resource.  Will affect overall throughput and response time.  This resource will be your bottleneck. 
 
3)Many waits for different resources.  Tougher to determine root cause.  Best idea is to begin investigating a few of the waits and note any common themes to investigate further. 
 
4)other.  Again, harder to determine root cause. 
 
 
Blocking Chains

This is the law of transitivity in action.  If there is a lot of blocking going on you need to determine the head blocker.  So, a may be blocking b, which is reported as blocking c.  In this case you need to investigate a first.  To find the head blocker just find the blocking task that is itself not blocked. 
 
select blocking_task_address as head_blocker_task_address,

          blocking_session_id as head_blocker_session_id

from sys.dm_os_waiting_tasks

where blocking_task_address is not null OR blocking_session_id is not null

except

select waiting_task_address, session_id

from sys.dm_os_waiting_tasks

where blocking_task_address is not null OR

        blocking_session_id is not null
the query identifies a head blocker as a task blocking something else that itself is not blocked.  But not all waiting tasks without an identified blocker are head blockers...they may be waiting on some resource that can't be reported on.
 

Add new comment