Skip to content
DBAExperts
SQL Server Diagnostics

How to view and resolve blocking in SQL Server (sp_who2 and DMVs)

When one session waits on another, the application feels slow or frozen. Here you identify who is blocking whom and how to release it carefully.

  1. 1

    Quick view with sp_who2

    Shows active sessions, and the BlkBy column indicates which SPID is doing the blocking. It is the first stop for a quick look.

    EXEC sp_who2;
-- Fijate en la columna 'BlkBy': si tiene un numero,
-- ese SPID esta esperando a otra sesion.
  2. 2

    Identify the blocking chain with DMVs

    sys.dm_exec_requests shows which session is blocked (blocking_session_id) and what it is waiting on. More precise than sp_who2.

    SELECT r.session_id AS bloqueada,
       r.blocking_session_id AS bloqueadora,
       r.wait_type, r.wait_time, r.wait_resource,
       t.text AS consulta
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;
  3. 3

    Inspect the specific locks

    sys.dm_tran_locks details which resources are locked and in what mode (shared, exclusive, etc.).

    SELECT resource_type, resource_database_id,
       request_mode, request_status, request_session_id
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';
  4. 4

    See what the blocking session is doing before you act

    Understand what the blocking session is running. Only as a last resort, and carefully, should you end it with KILL.

    -- Ver el ultimo comando de la sesion bloqueadora
DBCC INPUTBUFFER(58);

-- ULTIMO RECURSO: termina la sesion (hace rollback)
-- KILL 58;

// common mistake

KILL is destructive: it rolls back the open transaction, and if it is large the rollback can take as long as the original transaction. Never kill a session without understanding what it is doing. Many blocks come from long transactions or missing indexes, not from 'bad sessions'.

// when it's worth an expert

Recurring blocking and deadlocks are usually a symptom of a design or indexing problem. At dba.mx we diagnose the root cause (we do not just kill sessions) and fix it at a fixed price so it does not come back.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.