Encontrar a cadeia de bloqueio (lock)

Comando

SELECT r.session_id            AS waiting_spid,
       r.blocking_session_id  AS blocking_spid,
       r.wait_type,
       r.wait_time / 1000.0   AS wait_sec,
       r.status, r.command,
       DB_NAME(r.database_id) AS db,
       t.text                 AS waiting_query
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;

-- To find the head (root) blocker of the chain, look for the SPID whose
-- own blocking_session_id is not set (it is not blocked itself).

O que faz

Lista as sessões em espera e as sessões que as bloqueiam, com tempo de espera e a consulta que executam; torna visível a cadeia de locks.

Quando usar

Para encontrar a origem da contenção de locks em reclamações de "a aplicação trava / dá timeout".

Atenção

Confirme com o responsável pelo negócio antes de matar o bloqueador raiz (o SPID que não é bloqueado por ninguém, mas bloqueia os outros). Apenas consulta; sem risco.