Why Blocking Processes Bring SQL Server Environments to Their Knees

Blocking is one of the most common causes of SQL Server performance degradation in production environments. When one session holds a lock on a resource and another session is waiting to access it, you have a blocking process. Left unresolved, a single blocking session can cascade into dozens of waiting sessions, grinding application performance to a halt within minutes.

Finding the blocking process quickly and accurately is critical. Kill the wrong session and you risk unnecessary data loss or rolling back legitimate work. Spend too long investigating with the wrong tools and your users are staring at timeout errors while you scroll through hundreds of rows in sp_who2.

This article gives you a reliable, readable query to identify blocking processes in SQL Server, explains what each column tells you, and walks you through how to act on what you find.

What Causes Blocking in SQL Server?

SQL Server uses a lock-based concurrency model. When a session modifies data, it acquires locks to protect that data from being read or modified by other sessions until the transaction completes. This is by design. The problem arises when transactions hold locks longer than necessary, or when poorly written queries lock more rows or pages than they need to.

Common causes include:

  • Long-running transactions that aren't committed promptly
  • Missing indexes that force SQL Server to scan large portions of a table, holding locks across more rows
  • Explicit transactions left open by application code that doesn't handle errors properly
  • High lock escalation from row-level locks being promoted to table-level locks
  • Deadlocks, where two sessions are each waiting on a resource held by the other

Blocking and deadlocks are related but different. Blocking is one session waiting on another. A deadlock is a circular wait that SQL Server resolves automatically by killing one of the sessions. Either way, the first step is identifying exactly which session is causing the problem.

Why sp_who2 and sp_lock Aren't Always Enough

Experienced DBAs know sp_who2 and sp_lock well. They're fast, they're built-in, and in a pinch they do the job. But their output can be difficult to parse under pressure, particularly for less experienced team members or when you're dealing with a busy production system showing 200+ active sessions.

sp_who2 shows you the blocking session ID in the BlkBy column, but it doesn't show you the actual SQL being executed by either the blocked or blocking session. sp_lock shows lock details but requires you to cross-reference session IDs manually. Neither gives you the full picture in a single, readable output.

The query below uses SQL Server's Dynamic Management Views (DMVs) to pull everything you need into one result set: the database, the blocked and blocking session IDs, the object being locked, the resource type, the lock mode, and the actual SQL text for both sessions.

The Query: Identifying Blocking Processes Using DMVs

The following query joins several DMVs to surface the key information about any active blocking situation:

SELECT
    db.name AS DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) AS BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS RequestingText,
    h2.TEXT AS BlockingText,
    tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db 
    ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt 
    ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p 
    ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 
    ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 
    ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;

What Each Column Tells You

  • DBName - the database where the blocking is occurring
  • request_session_id - the session that is blocked and waiting
  • blocking_session_id - the session that is holding the lock and causing the wait
  • BlockedObjectName - the table or object being locked
  • resource_type - whether the lock is on a row, page, key, or object
  • RequestingText - the SQL statement being run by the blocked session
  • BlockingText - the SQL statement being run by the blocking session
  • request_mode - the type of lock being requested (shared, exclusive, update, etc.)

The two CROSS APPLY calls against sys.dm_exec_sql_text are what make this query particularly useful. They retrieve the actual SQL text for both sessions directly from the connection's most recent SQL handle. This means you can see exactly what the blocking session is doing, not just its session ID.

How to Interpret the Results

When you run this query during an active blocking event, focus on the blocking_session_id and BlockingText columns first. The blocking session is the one you need to investigate. Ask yourself:

  1. Is the blocking session actively executing a query, or is it idle with an open transaction?
  2. Is the SQL in BlockingText a long-running report or batch job that shouldn't be running during peak hours?
  3. Is the request_mode showing exclusive locks (X) that are preventing all other access?

An idle blocking session with an open transaction is a common pattern when application code opens a transaction and then waits for user input, or when an error occurs and the transaction isn't properly rolled back. In these cases, the session can sit there holding locks indefinitely.

If the BlockingText shows a legitimate, actively executing query, you may need to wait it out or investigate why it's taking so long. Missing indexes and statistics are frequent culprits.

How to Resolve a Blocking Process

Once you've identified the offending session, you can terminate it using the KILL command. Replace {session_id} with the blocking_session_id value from your query results:

KILL {session_id};

For example, if the blocking session ID is 57:

KILL 57;

A few important points before you use this command:

  • KILL will roll back any uncommitted transactions for that session. Depending on the size of the transaction, rollback can take as long as the original transaction took to run, sometimes longer.
  • Always confirm the session ID belongs to the blocking session, not the blocked one. Killing the wrong session achieves nothing and may cause additional data integrity issues.
  • In some environments you'll need sysadmin or ALTER ANY CONNECTION permissions to execute KILL.
  • You cannot kill your own session, and you cannot kill system sessions.

Microsoft's documentation on KILL is worth reviewing if you're unfamiliar with the rollback behaviour: KILL (Transact-SQL).

Preventing Blocking in the First Place

Resolving a blocking process in the moment is reactive. The better outcome is reducing how often blocking occurs in your environment. A few practical steps:

  • Review and optimise long-running queries identified in the blocking output
  • Ensure indexes support the access patterns of your most frequent queries
  • Keep transactions as short as possible and avoid user interaction inside a transaction
  • Consider READ_COMMITTED_SNAPSHOT isolation level (RCSI) for read-heavy workloads, which reduces reader-writer contention significantly
  • Schedule large batch jobs and report queries outside peak business hours
  • Set a reasonable LOCK_TIMEOUT in application code to prevent indefinite waits

Blocking that happens repeatedly on the same objects is a signal worth investigating thoroughly. It rarely resolves itself without addressing the underlying cause.

Key Takeaways

  • A blocking process in SQL Server occurs when one session holds a lock that another session needs. The DMV-based query in this article identifies both sessions and their SQL text in a single, readable output.
  • Always investigate the blocking_session_id and BlockingText before using KILL. Terminating the wrong session can cause unnecessary data loss.
  • The KILL command rolls back uncommitted work. On large transactions, rollback time can exceed the original transaction duration.
  • Repeated blocking on the same objects usually indicates missing indexes, long-running transactions, or poorly structured application code. These need to be fixed, not just killed.
  • DMVs like sys.dm_tran_locks, sys.dm_os_waiting_tasks, and sys.dm_exec_connections give you far more actionable detail than sp_who2 or sp_lock alone.

Blocking issues are one of the most common problems we resolve for clients across our managed support and SQL Server health check engagements. If your environment is experiencing regular blocking or you want a thorough review of your locking and concurrency configuration, DBA Services can help. Our health checks are designed to surface exactly these kinds of issues before they become critical incidents.