How to Detect and Resolve Blocking Sessions in SQL Server

If you've ever worked with SQL Server in a production environment, you've probably encountered a situation where queries suddenly slow down or applications become unresponsive. One of the most common causes of this issue is blocking.

Blocking occurs when one query holds a lock on a resource and prevents another query from accessing that same resource. While locking is a normal part of how SQL Server maintains data consistency, excessive blocking can seriously affect database performance.

In this article, we’ll explore:

1. What blocking is in SQL Server
2. How to detect blocking sessions
3. How to identify the blocking query
4. Ways to resolve and prevent blocking issues

What is Blocking in SQL Server?

Blocking happens when a transaction locks a resource (such as a table or row), and another transaction needs access to that same resource.

For example:

• Transaction A updates a row in a table
• Transaction B tries to read or update the same row
• Transaction B must wait until Transaction A completes

If Transaction A takes too long to finish, other queries start piling up behind it.

In production systems, this can cause:

• slow application performance

• timeouts

• long query wait times

• frustrated users

How to Detect Blocking Sessions

SQL Server provides several Dynamic Management Views (DMVs) that help identify blocking activity.

One of the easiest ways is to query sys.dm_exec_requests.

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource,
    status
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

What this query shows

• session_id → the waiting session

• blocking_session_id → the session causing the block

• wait_type → the type of wait occurring

• wait_time → how long the session has been waiting

If the blocking_session_id column contains a value, that means the session is being blocked by another process.

Identifying the Blocking Query

Once you find the blocking session ID, the next step is to determine which query is causing the block.

You can retrieve the query text using the following query:

SELECT
    r.session_id,
    r.blocking_session_id,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = <blocking_session_id>;

Replace with the session ID returned from the previous query.

This will show the SQL statement responsible for holding the lock.

Using sp_who2 for Quick Monitoring

SQL Server also includes a useful built-in stored procedure called sp_who2 that provides a quick overview of active sessions.

EXEC sp_who2;

Look at the BlkBy column.

If a session shows a value in this column, it means it is being blocked by another session.

Although it's an older diagnostic tool, many DBAs still use it for quick troubleshooting.

Resolving Blocking Sessions

Once you identify the blocking session, you have several options.

1. Investigate the query

Check if the query is performing unnecessary operations such as:

• table scans

• missing indexes

• large transactions

Optimizing the query may resolve the issue without terminating it.

2. Commit or rollback the transaction

Sometimes the blocking occurs because a transaction was left open longer than expected.

Completing the transaction will release the lock.

3. Terminate the blocking session

If the blocking query is causing major disruption, you may need to stop it using the KILL command.

KILL <session_id>;

Be cautious when using this command because SQL Server must roll back the transaction before releasing the locks.

Monitoring Long-Running Queries

In many cases, blocking occurs because a query has been running for too long.

If you need to identify and terminate long-running queries, you can follow this guide: How to Find and Kill Long-Running Queries in SQL Server This helps DBAs quickly detect problematic queries before they start blocking other sessions.

Best Practices to Prevent Blocking

While blocking can't always be avoided, good database practices can reduce its frequency.

Keep transactions short
Long transactions hold locks longer and increase the chance of blocking.

Create proper indexes
Missing indexes often force SQL Server to scan entire tables.

Avoid unnecessary locks
Design queries that access only the data they need.

Monitor database activity regularly
Routine monitoring helps detect performance issues early.

Final Thoughts

Blocking is a normal behavior in SQL Server, but when it becomes excessive, it can significantly affect database performance.

By using SQL Server’s built-in monitoring tools and DMVs, DBAs can quickly:

• detect blocking sessions

• identify problematic queries

• resolve issues before they impact users

Understanding how blocking works and how to troubleshoot it is an essential skill for anyone managing SQL Server environments.