Originally published at SQL & Cloud Lab .

Blocking happens when one session holds a lock and another session needs it. A little blocking is normal and clears in milliseconds. The problem is a blocking chain : one session holds locks for a long time, others queue behind it, and then more queue behind them. Users see a frozen application. The key is to find the session at the front of the chain, the head blocker .

Step 1: see who is blocked right now

SELECT r . session_id , r . blocking_session_id , r . wait_type , r . wait_time / 1000 AS wait_seconds , r . status , DB_NAME ( r . database_id ) AS database_name , t . text AS query_text FROM sys . dm_exec_requests r CROSS APPLY sys . dm_exec_sql_text ( r . sql_handle ) t WHERE r . blocking_session_id <> 0 ORDER BY r . wait_time DESC ;

Enter fullscreen mode

Exit fullscreen mode

Each row is a session that is waiting. blocking_session_id tells you who it is waiting for, and lock waits show up as LCK_M_* wait types.…