9.2 Session Blocking and Dynamic Management Views

Blocking vs Deadlocking

Blocking is a normal consequence of locking: session A holds a lock on a resource (row, page, key, table) that session B needs, so B waits. Blocking becomes a problem only when wait times grow. Deadlocking is a special case: two (or more) sessions each hold a lock the other needs, forming a cycle. SQL Server detects the cycle and picks a deadlock victim - the cheaper session to roll back - and raises error 1205 to that session. The key exam distinction: blocking is a wait, deadlocking is an error; blocking can resolve itself when the blocker commits, deadlocking never resolves without intervention.

A blocking chain forms when session A blocks B, B blocks C, C blocks D, and so on. The session at the root (A) is the head blocker. The single most important diagnostic principle: always find and address the head blocker first. Killing or rolling back a non-head session only shortens the chain by one; resolving the head blocker clears the entire chain.

Wait Types That Signal Blocking and I/O Pressure

The wait_type column in sys.dm_os_waiting_tasks and sys.dm_exec_requests tells you why a session is waiting. Two families are easily confused:

The trap: PAGEIOLATCH is a latch wait, not a lock wait. A scenario that says "PAGEIOLATCH waits are high, so I will switch to snapshot isolation" is wrong - snapshot isolation addresses lock waits (LCK_M_*), not buffer-pool I/O. The correct remedy for PAGEIOLATCH is to reduce I/O (indexes, covering indexes, more memory) or speed up the storage.

The Core DMVs for Blocking Investigation

The diagnostic query pattern joins five DMVs:

SELECT
    s.session_id, s.host_name, s.program_name, s.status AS session_status,
    r.status AS request_status, r.wait_type, r.wait_resource, r.wait_time_ms,
    r.blocking_session_id, r.command,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
        END) AS statement_text,
    DB_NAME(r.database_id) AS db_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.blocking_session_id, r.wait_time_ms DESC;

The DMVs and what each contributes:

• sys.dm_exec_sessions: one row per session. Host, program, login, status (sleeping vs running), and is_user_process to filter system sessions.
• sys.dm_exec_requests: one row per active request. blocking_session_id is nonzero when the request is blocked; wait_type, wait_resource, and wait_time_ms describe the wait. statement_start_offset/statement_end_offset let you extract the exact statement from a multi-statement batch.
• sys.dm_tran_locks: one row per active lock. resource_associated_entity_id joins to a hobt_id or object_id; request_status is GRANT or WAIT. Use this to see exactly what resource a blocker holds.
• sys.dm_os_waiting_tasks: one row per waiting task. blocking_session_id here also identifies the blocker; wait_duration_ms and resource_description give detail.
• sys.dm_exec_sql_text: returns the full text of a batch given a sql_handle; cross-applied against the request or session.

To find the head blocker: query sys.dm_exec_requests where blocking_session_id <> 0, then identify sessions whose session_id appears as a blocker but whose own blocking_session_id is 0 - those are head blockers. Alternatively, the blocking_chain pattern: walk blocking_session_id until you reach a session that blocks nothing.

Blocked Process Report and Deadlock Graphs

SQL Server can capture blocking and deadlocks as events. Trace flags 1222 and 1204 write deadlock information to the SQL Server error log (1222 is the modern default; 1204 is the legacy format). The blocked process report is an XML report raised when a session is blocked longer than the blocked process threshold (configured via sp_configure 'blocked process threshold', in seconds); it fires a BlockedProcessReport event consumed by Extended Events.

The richest deadlock source is the system_health Extended Event session, which is on by default on SQL Server and captures xml_deadlock_report. To read recent deadlocks:

SELECT XEvent.query('(event/data[@name="deadlock"]/value/deadlock)[1]') AS deadlock_graph,
       XEvent.value('(event/@timestamp)[1]', 'datetime2') AS event_time
FROM (SELECT CAST(target_data AS xml) AS target_data
      FROM sys.dm_xe_session_targets st
      JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
      WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer') AS t
CROSS APPLY target_data.nodes('//event[@name="xml_deadlock_report"]') AS XEvent(x);

On Azure SQL Database, system_health is not available; you instead capture deadlocks with a custom Extended Event session targeting the sqlserver.xml_deadlock_report event, stream the SQLInsights and Deadlocks diagnostic-log categories to a Log Analytics workspace, or read the deadlock platform metric in Azure Monitor. The deadlock graph shows the victim, the resources, the locks, and the statements from each session - read it to find the cheapest fix.

Resolving Blocking Durably

Releasing a chain by killing the head blocker (KILL <session_id>) is a temporary measure. The durable fixes:

• Enable Read Committed Snapshot Isolation (RCSI) or snapshot isolation: readers get a row-versioned view and do not need shared locks, eliminating most reader/writer blocking. RCSI is enabled at the database level (ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON) and is the default on Azure SQL Database - a common reason workloads stop blocking when migrated to Azure. Snapshot isolation requires code changes to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT and careful handling of update conflicts.
• Tune indexes so queries touch fewer rows and hold locks for shorter durations; a missing index often manifests as blocking on a large scan.
• Shorten transactions: commit in the smallest meaningful unit; do not hold transactions open across user input or network calls.
• Use a lower isolation level when correctness permits - but only after RCSI, which gives you read consistency without locks, is ruled out.
• Batch large updates to reduce lock duration and lock escalation.

A recurring exam scenario: an OLTP workload migrated from on-premises SQL Server to Azure SQL Database suddenly has much less blocking - the answer is RCSI, which is on by default for Azure SQL Database and off by default for SQL Server.